Salesforce - SOQL Use mod() or similar math functions in SELECT?

Question

I'm trying to query all Opportunities that have a Price that's not a whole number (no decimals) or if its price is not multiple of 10.

Im trying to find prices like: U$S 34,801.23 - U$S 56,103.69 - U$S 50,000.12 etc But not : U$S 49,500.00 - U$S 19,110.00 etc

There are a lot of opportunities in the database, and i can go through them by code but wanted to know if any of you can think of a way to achieve at leaast a part of this by query.

Answer 1

SOQL won't let you do any calculations. Check docs here and here:

You must supply a native value—other field names or calculations are not permitted

Generally speaking it's fieldName = value or datefieldname > YESTERDAY (few special literals for date handling).

Easiest would be to create a formula field in the record (think about them like calculated columns in views in normal databases) with your logic. It could be of text type, call it "weird price" ;) Here's the complete formula functions reference - you have MOD(), IF(condition, true, false) etc goodies.

The only caveat is that you can't GROUP BY formula (there also tricks to bypass that ;))

So yeah - if it's one time thing with poor reusability - filter with code. If you can accept the low cost (formulas don't use storage but you might need them for other purposes) - create a field.