+ Start a Discussion

SOQL ORDER BY variable - setting the variable

I have built a custom report using Visual Force. I am trying to give the end user the ability to sort the report by a few different fields. I have created a pick list on a Custom Object that contains the field names (API Names) that the user can sort by. In my class I query the Custom object to get the field the end user wants to sort by, I store that in a variable. I then want to use that vairble to order by my SOQL statement that builds the report.


Here is my Query to build the report:


String shipping = '%Small Parcel%';
String Stage = 'Closed Won';

String Query = 'SELECT Estimated_Ship_Date__c, Date_Shipped__c, Opportunity.CloseDate, Opportunity.Account.Name, From OpportunityLineItem WHERE Opportunity.StageName = :Stage and (NOT ShippingType__c like :shipping) ORDER BY ';
Query += SortBy ;

List<OpportunityLineItem> myOCR = Database.query(Query);

return myOCR;


When I hard code the SortBy Variable the query works perfectly however I need to set the SortBy varible to the result of another query and I cannot get the correct syntax. Here is what I have tried so far and none of these worked:


String SortBy = 'Select SortBy__C from Custom__c Where Name = FreightDashboard' ;


String SortBy = '(Select SortBy__C from Custom__c Where Name = FreightDashboard)' ;


String sortx = 'Select SortBy__C from Custom__c Where Name = FreightDashboard' ;
List<Custom__c> Sortby = Database.query(sortx);
Return Sortby;


list<Custom__c> Sortby;
list <Custom__c> customa = ([Select SortBy__C from Custom__c Where Name = 'FreightDashboard']);{
   for (Custom__c cx:customa){
   Sortby.add(cx.SortBy__C); //not sure on this syntax 



list<Custom__c> Sortby;

  public list<Custom__c> getCustom__c() {

        Sortby = ([Select SortBy__C from Custom__c Where Name = 'FreightDashboard']);

        return Sortby;



As you can probably tell I am new to SOQL. I have experience with SQL but I am struggling with this Order by variable.


Thank you,


David WrightDavid Wright

I'm a bit confused here as to what exactly you're trying to accomplish. It would appear that you're doing an arbitrarily defined query for an object or custom setting with the intention of returning a list of fields to sort by. You then want to append the results of that query as the field list to sort the results of database.query(Query);


Is that correct?


Can you give some more detail about what exactly is being stored in the SortBy__c field that you are querying for?





You need to comma delimit them.


String sortBy = '';
list <Custom__c> customa = ([Select SortBy__C from Custom__c Where Name = 'FreightDashboard']);
  for (Custom__c cx:customa)
   sortBy +=cx.SortBy__c + ', '; //not sure on this syntax 

if (sortBy.length() > 0)//cant remember if this is length or size
  sortBy = sortBy.substring(0, sortBy.length() - 3);