CODEWITHSUNDEEP
sqlpostgresql
avi
avi

Reputation: 1846

use the calculation from CASE in the result in PostgreSQL

I have the following view:

CREATE OR REPLACE VIEW view_a AS 
 SELECT distinct p.partid, case when 
                (SELECT EXTRACT(YEAR FROM age) * 12 + EXTRACT(MONTH FROM age) 
                FROM age(NOW(), LEAST(pp.createdate)) AS t(age)) AS months_between

                <24 then months_between::integer else 24  end as timeframe
   FROM ....
  WHERE ....;

This view takes createdate (time stamp of the oldest recorded creation date per partid) and calculates the months passed since this date to NOW() same as done here if the result is smaller than 24 (months) I want this result otherwise I want 24.

This currently not working it doesn't accept the AS months_between in the middle of the case.

How can I do that?

EDIT :

For example this query works:

 SELECT distinct p.partid, case when 
                (SELECT EXTRACT(YEAR FROM age) * 12 + EXTRACT(MONTH FROM age) 
                FROM age(NOW(), LEAST(pp.createdate)) AS t(age)) 
                <24 then 5 else 24  end as timeframe
   FROM ....
  WHERE ....;

However it will return 5 or 24 and it's not what I want. I want it to return the value calculated in the CASE.

To simplify the question basically the form is:

case when calculated_value < 24 then calculated_value  else 24 end;

I want to be able to return the calculated_value in the case.

Upvotes: 0

Views: 1269

Answers (1)

S. Stas
S. Stas

Reputation: 810

You could try to move months_between calculation to the common table expression (WITH query), something like this:

WITH timeframe_table AS (
    SELECT EXTRACT(YEAR FROM age) * 12 + EXTRACT(MONTH FROM age) 
    FROM age(NOW(), LEAST(pp.createdate)) AS months_between 
) SELECT
    CASE when months_between< 24 then months_between else 24 as timeframe
        FROM ..., timeframe_table
        WHERE...

You may need to add some id field to timeframe_table to have some join condition with your table.

Upvotes: 2

Related Questions