[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    [SQL] Returning Multiple Values from CASE statement?
From:       C F <tacnaboyz () yahoo ! com>
Date:       2003-05-29 16:05:56
[Download RAW message or body]

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).


[Attachment #3 (text/html)]

<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).



[prev in list] [next in list] [prev in thread] [next in thread]