function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
colemabcolemab 

How to create a loop based on a query from a string?

Standard loops with inline SOQL look something like this:

 

for(Item_Detail__c S : [SELECT Amount__c, Name, Id FROM Item_Detail__c
WHERE Item__c in :ItemsSet]) {
// code inside the loop goes here
}

 

Due to the lack of a Select * in SOQL, I would like to generate the select part of the query but at the end append the :ItemsSet part.

 

This will allow me to build a query on all fields but have the where condition dependant on the set and be able to loop thru the results of the query.

 

Does anyone have an example of how to do this?

 

Thanks!

Best Answer chosen by Admin (Salesforce Developers) 
colemabcolemab

A simple change got it working.   The bind variable is set in a static string.

 

Here is the updated code:

 

List<Item_Detail__c> ItemsSetList  = new List<Item_Detail__c>();

// build our query for items
SelectSOQLQuery = Cloner.GenerateSelectAllFieldsSOQL('Item_Detail__c','');

SelectSOQLQuery += ' WHERE Item__c in :ItemsSet';

system.debug('>>>> Sub-Item\'s Query is: ' + SelectSOQLQuery);

ItemsSetList = Database.query(SelectSOQLQuery);

 

Thanks for the help!

All Answers

Jake GmerekJake Gmerek

You need to set it up a little differently:

 

List<Item_Detail_c> slist  = new List<Item_Detail__c>;

 

String query = 'SELECT Amount__c, Name, Id FROM Item_Detail__c WHERE Item__c in :' + someVariable;

 

slist = Database.query(query);

 

for (Item_Detail__c s: slist){

  //code

}

 

where someVariable is the string that you want to dynamically generate.

 

Let me know if you have any questions.

colemabcolemab

Thank you for the reply.  When I get a moment, I will try it out.  Of course, if it works I will mark your post as the solution.  Thanks again.

 

More to follow ....

colemabcolemab

I just now got a chance to look at this and it isn't working.

 

This code:

List<Item_Detail__c> ItemsSetList  = new List<Item_Detail__c>();

// build our query for items
SelectSOQLQuery = Cloner.GenerateSelectAllFieldsSOQL('Item_Detail__c','');

SelectSOQLQuery += ' WHERE Item__c in :' + ItemsSet;

system.debug('>>>> Sub-Item\'s Query is: ' + SelectSOQLQuery);

ItemsSetList = Database.query(SelectSOQLQuery);

 

Throws this exception:  EXCEPTION_THROWN|[135]|System.QueryException: unexpected token: '{'

 

From the database query line.  The debug log shows this is the query:

 

SELECT item_detail_name__c,  createddate,  line_item_total__c,  createdbyid,  lastmodifieddate,  connectionsentid,  id,  item__c,  isdeleted,  name,  quantity__c,  systemmodstamp,  lastmodifiedbyid,  amount__c FROM Item_Detail__c WHERE Item__c in :{a0BP0000001yiioMAA, a0BP0000001yiitMAA}



It looks like this way of 'building' a bind varaible into a SOQL string doesn't work.   Thanks for the reply though.  I am open to any other ideas.

 

Thanks

colemabcolemab

A simple change got it working.   The bind variable is set in a static string.

 

Here is the updated code:

 

List<Item_Detail__c> ItemsSetList  = new List<Item_Detail__c>();

// build our query for items
SelectSOQLQuery = Cloner.GenerateSelectAllFieldsSOQL('Item_Detail__c','');

SelectSOQLQuery += ' WHERE Item__c in :ItemsSet';

system.debug('>>>> Sub-Item\'s Query is: ' + SelectSOQLQuery);

ItemsSetList = Database.query(SelectSOQLQuery);

 

Thanks for the help!

This was selected as the best answer
Jake GmerekJake Gmerek

No problem