SQLITE Case Expression with variable to avoid double evaluation of expression

Question

Hi I have a query where a expression is gonna be evaluated in most cases twice. I only want it evaluated once. I want to assign

CAST(ROUND(VOLUME - ((CAPACITY*1.0)/TIMEFRAME)*(STRFTIME('%s','now') - UPDATED_TIMESTAMP)) AS INT)

to a variable, so it doesn't get evaluated twice. How can I do this in one sql query?

SELECT CASE
           WHEN CAST(ROUND(VOLUME - ((CAPACITY*1.0)/TIMEFRAME)*(STRFTIME('%s','now') - UPDATED_TIMESTAMP)) AS INT) > 0 THEN CAST(ROUND(VOLUME - ((CAPACITY*1.0)/TIMEFRAME)*(STRFTIME('%s','now') - UPDATED_TIMESTAMP)) AS INT)
           ELSE 0
       END
FROM BUCKETS;

What do you mean when you say it's going to be evaluated twice? Sample data and desired results would be helpful. — sgeddes, Oct 07 '14 at 22:29
if CAST(ROUND(VOLUME - ((CAPACITY*1.0)/TIMEFRAME)*(STRFTIME('%s','now') - UPDATED_TIMESTAMP)) AS INT) > 0 then the same expression will be evaluated and returned as as result. The expression always returns integers — ppone, Oct 07 '14 at 22:31
If an expression occurs multiple times in the sames statement it IS only evaluated once. — Dale M, Oct 07 '14 at 22:34
@DaleM if that is an answer could you please post it as an answer instead of a comment. — ppone, Oct 07 '14 at 22:35
can you do this? [Declare variable in sqlite and use it](http://stackoverflow.com/questions/7739444/declare-variable-in-sqlite-and-use-it) — gloomy.penguin, Oct 10 '14 at 21:20

score 8 · Accepted Answer · answered Oct 12 '14 at 22:18

You can evaluate expression in subquery and then use it's name (expr) in query:

SELECT CASE
           WHEN expr > 0 THEN expr
           ELSE 0
       END
FROM (
  SELECT
    -- add BUCKETS.*, here if you need other BUCKETS fields return to query
    CAST(ROUND(VOLUME - ((CAPACITY*1.0)/TIMEFRAME)*(STRFTIME('%s','now') - UPDATED_TIMESTAMP)) AS INT) AS expr 
  FROM BUCKETS
) b

score 4 · Answer 2 · answered Oct 12 '14 at 22:26

+1 to the answer from @Rimas.

For what it's worth, here's a similar solution using a Common Table Expression.

WITH buckets_enhanced AS (
  SELECT CAST(ROUND(VOLUME - ((CAPACITY*1.0)/TIMEFRAME)*(STRFTIME('%s','now') - UPDATED_TIMESTAMP)) AS INT) AS expr 
  FROM BUCKETS
)
SELECT CASE WHEN expr > 0 THEN expr ELSE 0 END 
FROM buckets_enhanced;

It's useful to learn to use CTE's because they're more powerful than simply a derived-table subquery.
For example, you can use the CTE multiple times in the outer query, as in a self-join.

SQLITE Case Expression with variable to avoid double evaluation of expression

2 Answers2