From: | Thomas Kellerer <spam_eater(at)gmx(dot)net> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: How can I re-use an expression in a SELECT? |
Date: | 2012-07-06 13:26:02 |
Message-ID: | jt6p1l$vrr$1@dough.gmane.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Rob Richardson, 06.07.2012 15:04:
> Greetings!
>
> It seems to me, from my uneducated perspective, that if I have a
> calculated result with a name in a SELECT query, then I ought to be
> able to re-use the name later in the statement:
>
> select charge,
> case when c.rev_heat_time = 0 then c.pred_heat_time
> else c.rev_heat_time
> end as heat_time,
> case when c.rev_cool_time = 0 then c.pred_cool_time
> else c.rev_cool_time
> end as cool_time,
> fire_date + interval '1 minute' * heat_time as end_of_fire
> from charge c
>
> Of course, that fails because heat_time cannot be used to calculate
> end_of_fire. I would have to repeat the case statement that defines
> it instead. How can I avoid repeating complicated pieces of SELECT
> queries like this?
>
> Thanks very much!
>
> RobR
>
You need a derived table (aka sub-select):
select charge,
heat_time,
cool_time,
fire_date +interval '1 minute' * heat_time as end_of_fire
from (
select charge,
CASE
WHEN c.rev_heat_time = 0 THEN c.pred_heat_time
ELSE c.rev_heat_time
END AS heat_time,
CASE
WHEN c.rev_cool_time = 0 THEN c.pred_cool_time
ELSE c.rev_cool_time
END AS cool_time
from charge c
) t
From | Date | Subject | |
---|---|---|---|
Next Message | Oliveiros d'Azevedo Cristina | 2012-07-06 13:30:15 | Re: How can I re-use an expression in a SELECT? |
Previous Message | Rob Richardson | 2012-07-06 13:04:57 | How can I re-use an expression in a SELECT? |