Thread: How can I re-use an expression in a SELECT?

How can I re-use an expression in a SELECT?

From
Rob Richardson
Date:

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

Re: How can I re-use an expression in a SELECT?

From
Thomas Kellerer
Date:
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


Re: How can I re-use an expression in a SELECT?

From
"Oliveiros d'Azevedo Cristina"
Date:
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 -----
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

Re: How can I re-use an expression in a SELECT?

From
Rob Richardson
Date:

I understand.  Thanks very much!

 

RobR

 

From: Oliveiros d'Azevedo Cristina [mailto:oliveiros.cristina@marktest.pt]
Sent: Friday, July 06, 2012 9:30 AM
To: Rob Richardson; pgsql-novice@postgresql.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 -----

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

Re: How can I re-use an expression in a SELECT?

From
"Oliveiros d'Azevedo Cristina"
Date:
U're welcome :-)
 
Best,
Oliveiros
----- Original Message -----
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 [mailto:oliveiros.cristina@marktest.pt]
Sent: Friday, July 06, 2012 9:30 AM
To: Rob Richardson; pgsql-novice@postgresql.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 -----

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