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 select list (so it really is text), and IntField is number (2, 0). Changing the types of these fields is not possible.

I would like to write SOQL as;

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

Obviously Cast(TextField as Integer) DOES NOT work.

Any advice on converting types to SOQL. Regular APEX functions (e.g. integer.valueof) don't seem to help here.

thanks

Answer 1

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

Create a numerical 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.