SOQL Type conversion (SalesForce.com)

Question

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

Answer 1

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.