Apex Code Development You need to sign in to do that
Don't have an account?
ZenMongoose Computed Columns in Soql Query
In SQL I can write a select as:
select cost, cost * :miles as flat-rate from abc where orig = 'Minneapolis' and dest = 'Dallas'
Since you can not use operators in SOQL - how do get around this limitation? Typically I will get back mulitple rows of rate for diffent time periods.
For instance the results should look like
time orig dest rate/mile miles flat rate
last 7 days MN TX 1.00 1100 $1100
8 -20 days MN TX 1.10 1100 $1210
Any help is greatly appreciated!
Adding a formula field is the usual solution
here you might also need some aggregate query ("group by" )
Thanks for the reply Nick. Thought about that, but here's my issue.
The business need is the user enters the origin, destination and miles then return the per mile and flat rate (calculated)by time period (there are usually 3 or more time periods, all with different rates.
rates are stored by state in the rate_matirx object . MN to TX is say 1.25 / mile. The request is based on actual miles, say 1100. The next request could be for 1125 miles. To use a formula field I think I would need to create a request object that stores the request, then links out to rates to calculate this flat rate (1100 * 1.25). This seems like overkill because there isn't a business need to store the request. To use a formula field wouldn't I need to update the miles on this record every time there is a requested rate. We could have numerous requests pounding this exact record every second to update miles. I don't see that working.
Maybe I need to store the request in a seperate object and have a trigger to go to the rate_matrix, pull the rates and calc the flat rate, but then I need to replicate the request once for each rate that comes back.
I hope I'm missing something here because this seems waaaay too complicated for something that SOQL should support. If I'm not missing something then Ideas page, hear I come!
Ah okay, I see what you're saying. Yeah the formula field lets you calculate on data in the records you've saved into salesforce. SOQL is indeed limited, only actual fields can be selected, or the aggregate functions. Is this a visualforce page then? you'll probably need to just query the rates and the calculate the results in your Apex.
I think the limitation of not being able to include formulas in SOQL can be taken care of by using formula fields. If we consider your example I think cost and miles are fields in same record, if that is so you can have a formula field- "Flat rate" = cost* miles , and you that include the reference of this formula field in the SOQL query.
select cost, miles, Flat-rate from abc where orig = 'Minneapolis' and dest = 'Dallas'
Did this answer your question? If not, let me know what didn't work, or if so, please mark it solved.