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;

Answer 1

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