Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Andreas GerogiannisAndreas Gerogiannis 

Can I compare numeric values from an auto number data type field in an SQL query?

I have to iterate between thousand of records and I was thinking of using a uniqueid field that I have, but I cant use the numeric value of it. Seems its treated like string. Is there any way that I can compare numeric values with auto number values?

AND (uniqueid__c > i AND uniqueid__c < i+5000)
Best Answer chosen by Andreas Gerogiannis
UC InnovationUC Innovation
You might not be able to achieve what you want using Aggregate SOQL query. Instead I recommend querying all of the records and looping through them while picking the ones that do fall within the range that you want. E.g.
  
// Store query results here in variable queryResults

// declare set of object you need in set recordSet

for (result : queryResults) {
    if (Integer.valueOf(result.uniqueid__c) > i && Integer.valueOf(result.uniqueid__c) < i+5000) {
		recordSet.add(result);
    }
}

Hope this helps

AM
 

All Answers

UC InnovationUC Innovation
Hi,

This can be done easily if the formatting of the autonumber field is only numeric. Otherwise you might have to parse the string or use some sort of sortible interface to achieve what you want. 

Hope this helps!

AM
Andreas GerogiannisAndreas Gerogiannis
Display Format{00000000}
Andreas GerogiannisAndreas Gerogiannis
I tried this .. AND (Integer.valueOf(uniqueid__c) > i AND Integer.valueOf(uniqueid__c) < i+5000)
And it gives me ... expecting a colon, found 'i'
Andreas GerogiannisAndreas Gerogiannis
i tried this AND (Integer.valueOf('uniqueid__c') > i AND Integer.valueOf('uniqueid__c') < i+5000)
and it gives me ... unexpected token: 'uniqueid__c'
Andreas GerogiannisAndreas Gerogiannis
Stupid of me, I had to include the field in the SELECT first. But even when I included ...

I used ...   (Integer.valueOf(uniqueid__c) > :i AND Integer.valueOf(uniqueid__c) < :i+5000)

And the result is
Invalid aggregate function: Integer.valueOf
UC InnovationUC Innovation
You might not be able to achieve what you want using Aggregate SOQL query. Instead I recommend querying all of the records and looping through them while picking the ones that do fall within the range that you want. E.g.
  
// Store query results here in variable queryResults

// declare set of object you need in set recordSet

for (result : queryResults) {
    if (Integer.valueOf(result.uniqueid__c) > i && Integer.valueOf(result.uniqueid__c) < i+5000) {
		recordSet.add(result);
    }
}

Hope this helps

AM
 
This was selected as the best answer
Andreas GerogiannisAndreas Gerogiannis
Finally I used batch APEX for this. So thanks for confirnation