-1

I have below SOQL statement in an Apex class, Column Promotion returns true or false, If the value is true then it should return 'Promo Enabled' otherwise ''. Can we handle this within the select query?

@AuraEnabled
    public static Object searchLocations(Decimal lon, Decimal lat, Integer radius, Integer resultSize){
        return Database.query('SELECT Id, Name, Street, City, State, PostalCode, Country, Primary_Phone_Number__c, Promotion,'+
            'DISTANCE(Address, GEOLOCATION('+lat+', '+lon+'), \'mi\') '+
            'FROM ServiceTerritory WHERE DISTANCE(Address, GEOLOCATION('+lat+', '+lon+'), \'mi\')>0 '+
            'AND DISTANCE(Address, GEOLOCATION('+lat+', '+lon+'), \'mi\')<'+radius+
            ' AND IsActive=TRUE '+
            'ORDER BY DISTANCE(Address, GEOLOCATION('+lat+', '+lon+'), \'mi\') LIMIT '+resultSize);
    }
GVMK
  • 27
  • 1
  • 1
  • 6

1 Answers1

0

SOQL has only handful of functions and no arithmetic for example, you can't add 2 fields together or compare field to field (only field to value)

Some are here:

  1. https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_agg_functions.htm
  2. https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_date_functions.htm
  3. https://salesforce.stackexchange.com/questions/166372/all-functions-available-in-soql

You'd need to create a custom formula (text) field and include it in the query (it'll be calculated in runtime, they act a bit like views in normal SQL).

Alternatively return some custom data structure (List<Wrapper> where each entry is a helper class consisting of ServiceTerritory and Boolean) or do the calculation client-side.

eyescream
  • 18,088
  • 2
  • 34
  • 46