Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
ssoftwaressoftware 

dynamic soql issue

Hi All,

 

The following works and returns the result as expected:

 

private List<Opportunity> opps;

 

opps = [SELECT Id, Name, Amount, CloseDate, StageName,LeadSource FROM Opportunity WHERE Id IN ('006U00000026EE0IAM','006U00000026EDs')];



However, I am trying to make it work this way, but the query does not return any results.

 

String sClause = '\'' + '006U00000026EE0IAM' + '\'' + ',' + '\'' + '006U00000026EE0IAM' + '\'';

 

opps = [SELECT Id, Name, Amount, CloseDate, StageName,LeadSource FROM Opportunity WHERE Id IN (:sClause)]; 

 

Can someone let me know what is the proper syntax?

 

Thanks in advance.

      


Best Answer chosen by Admin (Salesforce Developers) 
mngmng

From what I've tried, if you choose to place variables inside native SOQL like that, it is expected that each value be its own Id. Meaning, each Id would need its own variable assignment, and each of those would have to be passed in. So for those two particular Ids, it'd look something like

 

String opportunityId = '006U00000026EE0IAM';
String opportunityId2 = '006U00000026EDs';

...
opps = [ SELECT Id FROM Opportunity WHERE Id IN (:opportunityId, :opportunityId2) ];
...

 Note that the Ids are NOT escaped.

 

Alternatively, the approach that you've used is similar to how the dynamic database queries could work.

String opportunityIds = '( \'006U00000026EDs\', \'006U00000026EE0IAM\' )';

String query = 'SELECT Id FROM Opportunity WHERE Id IN ' + opportunityIds;

List<Opportunity> opportunities = Database.query( query );

 

 

Also worth noting, if you were to store the Ids in a set, you could use the same IN operator in native SOQL to query based on the set.

Set<Id> opportunityIds = new Set<Id>{ '006U00000026EE0IAM', '006U00000026EDs' };

List<Opportunity> opportunities = [ SELECT Id FROM Opportunity WHERE Id IN :opportunityIds ];

 

All Answers

ssoftwaressoftware

BTW, there was a small typo. I meant:

 

String sClause = '\'' + '006U00000026EE0IAM' + '\'' + ',' + '\'' + '006U00000026EDs' + '\'';


mngmng

From what I've tried, if you choose to place variables inside native SOQL like that, it is expected that each value be its own Id. Meaning, each Id would need its own variable assignment, and each of those would have to be passed in. So for those two particular Ids, it'd look something like

 

String opportunityId = '006U00000026EE0IAM';
String opportunityId2 = '006U00000026EDs';

...
opps = [ SELECT Id FROM Opportunity WHERE Id IN (:opportunityId, :opportunityId2) ];
...

 Note that the Ids are NOT escaped.

 

Alternatively, the approach that you've used is similar to how the dynamic database queries could work.

String opportunityIds = '( \'006U00000026EDs\', \'006U00000026EE0IAM\' )';

String query = 'SELECT Id FROM Opportunity WHERE Id IN ' + opportunityIds;

List<Opportunity> opportunities = Database.query( query );

 

 

Also worth noting, if you were to store the Ids in a set, you could use the same IN operator in native SOQL to query based on the set.

Set<Id> opportunityIds = new Set<Id>{ '006U00000026EE0IAM', '006U00000026EDs' };

List<Opportunity> opportunities = [ SELECT Id FROM Opportunity WHERE Id IN :opportunityIds ];

 

This was selected as the best answer
ssoftwaressoftware

Thank you so much for your quick reply. For my situation, the second option you suggested works the best.