Greetings!

It seems to me, from my uneducated perspective, that if I have a calculatedresult 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

Rob Richardson, 06.07.2012 15:04:
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



--
Sent via pgsql-novice mailing list (pgsql-novice (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

I don't know any way of doing it without using a sub-query...

SELECT 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

) subquery



Surely there are more elegant ways of doing this in later versions.

I'm still using 8.3 ...



Best,

Oliveiros

----- Original Message -----
From: Rob Richardson
To: pgsql-novice (AT) postgresql (DOT) org
Sent: Friday, July 06, 2012 2:04 PM
Subject: [NOVICE] How can I re-use an expression in a SELECT?


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

I understand. Thanks very much!

RobR

From: Oliveiros d'Azevedo Cristina [mailtoliveiros.cristina (AT) marktest (DOT) pt]
Sent: Friday, July 06, 2012 9:30 AM
To: Rob Richardson; pgsql-novice (AT) postgresql (DOT) org
Subject: Re: [NOVICE] How can I re-use an expression in a SELECT?

I don't know any way of doing it without using a sub-query...

SELECT heat_time, cool_time, fire_date + interval '1 minute' * heat time asend_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
) subquery

Surely there are more elegant ways of doing this in later versions.
I'm still using 8.3 ...

Best,
Oliveiros
----- Original Message -----
From: Rob Richardson<mailto:RDRichardson (AT) rad-con (DOT) com>
To: pgsql-novice (AT) postgresql (DOT) o...esql (DOT) org>
Sent: Friday, July 06, 2012 2:04 PM
Subject: [NOVICE] How can I re-use an expression in a SELECT?

Greetings!

It seems to me, from my uneducated perspective, that if I have a calculatedresult 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

U're welcome :-)

Best,
Oliveiros
----- Original Message -----
From: Rob Richardson
To: pgsql-novice (AT) postgresql (DOT) org
Sent: Friday, July 06, 2012 2:45 PM
Subject: Re: [NOVICE] How can I re-use an expression in a SELECT?


I understand. Thanks very much!



RobR



From: Oliveiros d'Azevedo Cristina [mailtoliveiros.cristina (AT) marktest (DOT) pt]
Sent: Friday, July 06, 2012 9:30 AM
To: Rob Richardson; pgsql-novice (AT) postgresql (DOT) org
Subject: Re: [NOVICE] How can I re-use an expression in a SELECT?



I don't know any way of doing it without using a sub-query...



SELECT 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

) subquery



Surely there are more elegant ways of doing this in later versions.

I'm still using 8.3 ...



Best,

Oliveiros

----- Original Message -----

From: Rob Richardson

To: pgsql-novice (AT) postgresql (DOT) org

Sent: Friday, July 06, 2012 2:04 PM

Subject: [NOVICE] How can I re-use an expression in a SELECT?



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