Message-ID: <20030529160556.14465.qmail@web20413.mail.yahoo.com>
Date: Thu, 29 May 2003 09:05:56 -0700 (PDT)
From: C F <tacnaboyz@yahoo.com>
Subject: Returning Multiple Values from CASE statement?
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-1718942014-1054224356=:13098"

--0-1718942014-1054224356=:13098
Content-Type: text/plain; charset=us-ascii

Hello,
This is probably an odd request, but I have my reasons :)  Basically, what I need to do is return *multiple* values each for a number of criteria.  Imagine being able to return multiple values for each CASE statement... that's what I'm trying to do.  I can solve this with subqueries, but as you'll see it's *really* ugly, and I'm sure there's a better way (performance is key).  The other way obviously is to repeat the CASE statement for each value I want to return.... but that seems inefficient also.  So I was wondering if someone could take a look at my query and slap me up side the head and set me straight?
 
Here's the kind of query that would *like* to be able to do.....
 
select 
  (case when column1 = column2 then column5,column6) as alias5,alias6,
  (case when column3 = column4 then column7,column8) as alias7,alias8
from
  mytable
;

 
and here's the only ugly way I know how to do it......
 
select 
  a.*,
  (case a.alias2 = true then b.column5) as alias5,
  (case a.alias2 = true then b.column6) as alias6,
  (case a.alias4 = true then b.column7) as alias7,
  (case a.alias4 = true then b.column8) as alias8
from
  (select
  (case when column1 = column2 then true else false end) as alias2,
  (case when column3 = column4 then true else false end) as alias4
  from
  mytable) a,
  mytable b
where
  a.id = b.id
;

Thanks!!


---------------------------------
Do you Yahoo!?
Free online calendar with sync to Outlook(TM).
--0-1718942014-1054224356=:13098
Content-Type: text/html; charset=us-ascii

<DIV>Hello,</DIV>
<DIV>This is probably an odd request, but I have my reasons :)&nbsp; Basically, what I need to do is return *multiple* values each for a number of criteria.&nbsp; Imagine being able to return multiple values for each CASE statement... that's what I'm trying to do.&nbsp; I can solve this with subqueries, but as you'll see it's *really* ugly, and I'm sure there's a better way (performance is key).&nbsp; The other way obviously is to repeat the CASE statement for each value I want to return.... but that seems inefficient also.&nbsp; So I was wondering if someone could take a look at my query and slap me up side the head and set me straight?</DIV>
<DIV>&nbsp;</DIV>
<DIV>Here's the kind of query that would *like* to be able to do.....</DIV>
<DIV>&nbsp;</DIV>
<DIV>select <BR>&nbsp; (case when column1 = column2 then column5,column6) as alias5,alias6,<BR>&nbsp; (case when column3 = column4 then column7,column8) as alias7,alias8<BR>from<BR>&nbsp; mytable<BR>;<BR></DIV>
<DIV>&nbsp;</DIV>
<DIV>and here's the only ugly way I know how to do it......</DIV>
<DIV>&nbsp;</DIV>
<DIV>select <BR>&nbsp; a.*,<BR>&nbsp; (case a.alias2 = true then b.column5) as alias5,<BR>&nbsp; (case a.alias2 = true then b.column6) as alias6,<BR>&nbsp; (case a.alias4 = true then b.column7) as alias7,<BR>&nbsp; (case a.alias4 = true then b.column8) as alias8<BR>from<BR>&nbsp; (select<BR>&nbsp; (case when column1 = column2 then true else false end) as alias2,<BR>&nbsp; (case when column3 = column4 then true else false end) as alias4<BR>&nbsp; from<BR>&nbsp; mytable) a,<BR>&nbsp; mytable b<BR>where<BR>&nbsp; a.id = b.id<BR>;<BR></DIV>
<DIV>Thanks!!</DIV><p><hr SIZE=1>
Do you Yahoo!?<br>
Free <a href="http://us.rd.yahoo.com/mail_us/tag/*http://calendar.yahoo.com">online calendar</a> with sync to Outlook(TM).
--0-1718942014-1054224356=:13098--