Menu
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.
769
You can't use math in Percent SOQL statements. You have to do the math in Apex Code. Save this answer.
Show activity on this post. Knowing that new FIELDS function must have a LIMIT of at most 200, when used with ALL or CUSTOM keyword, the workaround below can still be useful. There is no way to Select * with SOQL. You can't use FIELDS(ALL) or FIELDS(CUSTOM) in Apex even with a LIMIT clause.
Salesforce does not allow direct field to field comparison in SOQL query. To achieve this you can create a formula field that will compare fields and return a value (such as true or false) which you can use in a WHERE clause.
The LIKE operator in SOQL and SOSL provides a mechanism for matching partial text strings and includes support for wildcards. The % and _ wildcards are supported for the LIKE operator. The % wildcard matches zero or more characters. The _ wildcard matches exactly one character.
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.
134
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
