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
SoozeeSoozee 

SOQL with order by variable

Hello,

I am trying to use a variable in the order by clause of a SOQL statement, like this:

 

enrollmentLongArr = [select E.Id, E.Name, E.Course__c, E.Course__r.Name, E.Case__c, E.Repeat_Class__c, E.Number_Of_Cases__c, E.Enrollment_Status__c, E.Date_of_Withdrawal__c, E.CreatedDate, E.Final_Grade__c from Enrollment__c E where E.Student__c = :accountId order by E.Course__r.Name :direction ];

 

where :direction will either be asc or desc.

 

I keep getting an error when I try to save my class:

save error: unexpected token ':'

 

What am I doing wrong?

Best Answer chosen by Admin (Salesforce Developers) 
Ispita_NavatarIspita_Navatar

Hi Newbie,

Your query :-

enrollmentLongArr = [select E.Id, E.Name, E.Course__c, E.Course__r.Name, E.Case__c, E.Repeat_Class__c, E.Number_Of_Cases__c, E.Enrollment_Status__c, E.Date_of_Withdrawal__c, E.CreatedDate, E.Final_Grade__c from Enrollment__c E where E.Student__c = :accountId order by E.Course__r.Name :direction ];

 

Try the following :-

enrollmentLongArr =' [select E.Id, E.Name, E.Course__c, E.Course__r.Name, E.Case__c, E.Repeat_Class__c, E.Number_Of_Cases__c, E.Enrollment_Status__c, E.Date_of_Withdrawal__c, E.CreatedDate, E.Final_Grade__c from Enrollment__c E where E.Student__c = :accountId order by E.Course__r.Name '  + direction + ']' ;

 

Did this answer your question? If not, let me know what didn't work, or if so, please mark it solved.

All Answers

CrocketCrocket

1) In your example,  would thnk the ORDER BY clause can only use field names from the WHERE clause.

2) I have only used parameterization in the WHERE clause.

3) However, the error you are seeing might be related to the missing comma . Something like the following would be allowable:

 

   ORDER BY E.Name, E.Course__c
CrocketCrocket

Correction, (1) should have been:

 

In your example,  I would thnk the ORDER BY clause can only use field names from the SELECT statement.
Ispita_NavatarIspita_Navatar

Hi Newbie,

Your query :-

enrollmentLongArr = [select E.Id, E.Name, E.Course__c, E.Course__r.Name, E.Case__c, E.Repeat_Class__c, E.Number_Of_Cases__c, E.Enrollment_Status__c, E.Date_of_Withdrawal__c, E.CreatedDate, E.Final_Grade__c from Enrollment__c E where E.Student__c = :accountId order by E.Course__r.Name :direction ];

 

Try the following :-

enrollmentLongArr =' [select E.Id, E.Name, E.Course__c, E.Course__r.Name, E.Case__c, E.Repeat_Class__c, E.Number_Of_Cases__c, E.Enrollment_Status__c, E.Date_of_Withdrawal__c, E.CreatedDate, E.Final_Grade__c from Enrollment__c E where E.Student__c = :accountId order by E.Course__r.Name '  + direction + ']' ;

 

Did this answer your question? If not, let me know what didn't work, or if so, please mark it solved.

This was selected as the best answer
Shashikant SharmaShashikant Sharma

You can not use dynamic order by claue in static query, need to execute dyanmic query, like this

String SOQLQuery = 'select E.Id, E.Name, E.Course__c, E.Course__r.Name, E.Case__c, E.Repeat_Class__c, E.Number_Of_Cases__c, E.Enrollment_Status__c, E.Date_of_Withdrawal__c, E.CreatedDate, E.Final_Grade__c from Enrollment__c E where E.Student__c = :accountId order by E.Course__r.Name ' + direction;
 
enrollmentLongArr  = database.query(SOQLQuery );

 Let me know if any issues in it.
SoozeeSoozee

Thank you, Shashikant!  This worked!

Shashikant Sharma wrote:

You can not use dynamic order by claue in static query, need to execute dyanmic query, like this

String SOQLQuery = 'select E.Id, E.Name, E.Course__c, E.Course__r.Name, E.Case__c, E.Repeat_Class__c, E.Number_Of_Cases__c, E.Enrollment_Status__c, E.Date_of_Withdrawal__c, E.CreatedDate, E.Final_Grade__c from Enrollment__c E where E.Student__c = :accountId order by E.Course__r.Name ' + direction;
 
enrollmentLongArr  = database.query(SOQLQuery );

 Let me know if any issues in it.

 
Shashikant SharmaShashikant Sharma

Your welcome Mate,

 

Please mark my answer as solution so that it helps others also.