Skip site navigation (1) Skip section navigation (2)

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

From: "Oliveiros d'Azevedo Cristina" <oliveiros(dot)cristina(at)marktest(dot)pt>
To: "Rob Richardson" <RDRichardson(at)rad-con(dot)com>,<pgsql-novice(at)postgresql(dot)org>
Subject: Re: How can I re-use an expression in a SELECT?
Date: 2012-07-06 13:30:15
Message-ID: 3398C4B3AD4A4572B2BA437C7C7D9AA9@marktestcr.marktest.pt (view raw or flat)
Thread:
Lists: 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

In response to

Responses

pgsql-novice by date

Next:From: Rob RichardsonDate: 2012-07-06 13:45:02
Subject: Re: How can I re-use an expression in a SELECT?
Previous:From: Thomas KellererDate: 2012-07-06 13:26:02
Subject: Re: How can I re-use an expression in a SELECT?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group