How to avoid the same calculations on column values over and over again in a select?
- by Peter
I sometimes write SELECTs on the form:
SELECT
a.col1+b.col2*c.col4 as calc_col1,
a.col1+b.col2*c.col4 + xxx as calc_col1_PLUS_MORE
FROM ....
INNER JOIN ...
ON a.col1+b.col2*c.col4 < d.some_threshold
WHERE a.col1+b.col2*c.col4 > 0
When the calculations get rather involved and used up to 3-5 times within the same SELECT, I would really like to refactor that out in a function or similar in order to
1) hopefully improve performance / make use of cache
2) avoid forgetting to update one of the 4 calculations when I at a later stage realize I need to change the calculation.
I usually have these selects within SPs.
Any ideas?
