function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

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

Dynamic Query + For loop

Can a dynamic query used inside a for loop ?

 

something like this,

 

for(account a: database.query('select id,name from account where id = :myID order by '+regExp+''))

 

NOTE : I tried SOMETHING like this, it has been giving following VF Error. 

 

caused by: System.QueryException: unexpected token: '('

Best Answer chosen by Admin (Salesforce Developers) 
Achilles21Achilles21

Hi All,

 

I figured the reason behind the error when we use a dynamic query.

It's because of the Today() function in Where clause.

 

the compiler is getting confused due to too many brackets. 

 

Simply, I assigned Today() to a variable and used it in my where clause.

 

One more thing, simple [select ..... ] won't work here as Order By is a dynamic query and therefore we have to use Database.Query()

All Answers

MagulanDuraipandianMagulanDuraipandian

Try like this

 

for(account a : [select id,name from account where id = :myID order by Name])

 

Regards,

Magulan D

Salesforce.com certified Force.com Developer.

SFDC Blog

SFDC Site

If this post is your solution, kindly mark this as the solution and give Kudos.

 

 
Achilles21Achilles21

Thanks! 

 

I tried this one but the problem is i dont know a way to have regular expression ( String ) in my ORDER BY clause using a simple [select ... ] query.

 

I have to use dynamic query i.e. Database.query(....).
Jeff MayJeff May

You should be able to use the [select] format but you'll have to escape the single quotes around your string.

 

[select field1, field2 from table where field1 = :var1 order by \'

 + regexp + '\' ]

 

 
Achilles21Achilles21

Thank You !

 

I don't know if I am doing something wrong here... This still is not working

Following is my query

 

viewData=[select id,Name from Custom__c where id = :myID order by \'+sortFullExp+'\ ];

 

 Error: Compile Error: line 161:238 no viable alternative at character '\' at line 161 column 238
Achilles21Achilles21

Hi All,

 

I figured the reason behind the error when we use a dynamic query.

It's because of the Today() function in Where clause.

 

the compiler is getting confused due to too many brackets. 

 

Simply, I assigned Today() to a variable and used it in my where clause.

 

One more thing, simple [select ..... ] won't work here as Order By is a dynamic query and therefore we have to use Database.Query()

This was selected as the best answer