11

The problem is that I need to compare two fields of different types in a SOQL query.

TextField is a Picklist (so really text) and IntField is a Number(2, 0). Changing the types of these fields is not possible.

I would like to write SOQL like;

SELECT Id FROM SomeObject__c
WHERE Cast(TextField as Integer) > IntField

Obviously Cast(TextField as Integer) does NOT work.

Any advise on type conversion within SOQL. The normal APEX functions (e.g. integer.valueof) don't seem to be of any help here.

Thanks

Christoph
  • 4,251
  • 3
  • 24
  • 38

1 Answers1

7

SOQL itself does not support casting but you can always lend a helping hand ;)

Create a Numeric formula field in SomeObject__c:

IF(ISNUMBER(TextField__c), VALUE(TextField__c), NULL)

or something similar depending on your definition of cast to int. Now you can use this field in a SOQL query.

mmix
  • 6,057
  • 3
  • 39
  • 65
  • Wanted to add two tidbits of information to this answers for the final solutions I needed; 1) You cannot compare two values on the object in SOQL - so I added that to the formula 2) You cannot call ISNUMBER (and may other functions) on PickLists - so I added a convert for that too Final formula looks like; IF(ISNUMBER(TEXT(TextField__c)), VALUE(TEXT(TextField__c)), NULL) - IntField Then you just eval against this in the SOQL query. Oh the joys of SFDC – Christoph May 09 '11 at 23:39
  • Oh, sorry, I missed the part where you said picklist or I would have given you a complete formula. I saw TextField in your code sample and went with that. – mmix May 10 '11 at 09:44
  • no worries - you sent me down the right path. Granted not what I wanted to hear, but I often find with SFDC that I can't do things in a way that feels natural (i.e. just have all this in the query) and have to employ workarounds. – Christoph May 10 '11 at 15:26