pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedorder by a "select as" determined by case statement
2+ messages / 2 participants
[nested] [flat]
* order by a "select as" determined by case statement
@ 2002-12-06 18:05 FatTony <fattony@comcast.net>
0 siblings, 1 reply; 2+ messages in thread
From: FatTony @ 2002-12-06 18:05 UTC (permalink / raw)
To: pgsql-sql
I'm not a db guru by any means so please forgive me if this has an easy
solution.
Scenario:
Want to sort by an alias for SELECT AGE(). Problem is the timestamps for
the SELECT AGE will be determined by the value of another column, thus
the use of CASE.
What I thought would work.
SELECT tblticket.ticketid,tblstatus.statusname,
CASE WHEN tblstatus.statusname = 'Closed'
THEN (SELECT AGE(tblticket.ticketclosed,tblticket.ticketcreated) as
ticketage)
ELSE (SELECT AGE(CURRENT_TIMESTAMP,tblticket.ticketcreated) as
ticketage)
END
FROM tblticket CROSS JOIN tblstatus
WHERE (tblticket.reasonid = 2) AND blah, blah, blah
ORDER BY ticketage ASC
CASE apparently completely ignores the ticketage alias. Anyone know how
to get around this?
Thank You.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: order by a "select as" determined by case statement
@ 2002-12-06 18:16 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: FatTony <fattony@comcast.net>
0 siblings, 0 replies; 2+ messages in thread
From: Stephan Szabo @ 2002-12-06 18:16 UTC (permalink / raw)
To: FatTony <fattony@comcast.net>; +Cc: pgsql-sql
On Fri, 6 Dec 2002, FatTony wrote:
> I'm not a db guru by any means so please forgive me if this has an easy
> solution.
>
> Scenario:
>
> Want to sort by an alias for SELECT AGE(). Problem is the timestamps for
> the SELECT AGE will be determined by the value of another column, thus
> the use of CASE.
>
> What I thought would work.
>
> SELECT tblticket.ticketid,tblstatus.statusname,
> CASE WHEN tblstatus.statusname = 'Closed'
> THEN (SELECT AGE(tblticket.ticketclosed,tblticket.ticketcreated) as
> ticketage)
> ELSE (SELECT AGE(CURRENT_TIMESTAMP,tblticket.ticketcreated) as
> ticketage)
> END
> FROM tblticket CROSS JOIN tblstatus
> WHERE (tblticket.reasonid = 2) AND blah, blah, blah
> ORDER BY ticketage ASC
>
> CASE apparently completely ignores the ticketage alias. Anyone know how
> to get around this?
Probably what you want is:
case when ...
then ...
else ...
end as ticketage
The aliases on the result columns of the subquery don't translate into the
alias of the case expression, but you can give the case expression an
alias instead.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-12-06 18:16 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-12-06 18:05 order by a "select as" determined by case statement FatTony <fattony@comcast.net>
2002-12-06 18:16 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox