Solved

I need to be able to return multiple values in my SQL case statement

Posted on 2016-10-20
12
105 Views
Last Modified: 2016-10-27
I have a task where I need to compare payment, dates, and other fields as well as get the Min date of a field.
If the comparison above is true I need to display two fields.

 I need to be able to return multiple values in my SQL case statement.  Is there a way other than repeating the Case statements?
0
Comment
Question by:Member_2_7964962
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +4
12 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 41853186
Can you explain with an example?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41853192
I agree that sample data and expected results would help a great deal.

You have also posted this in two pretty different database products.  What might work in one may not work in another.

What database do you need this for?

You can concatenate values in a single statement.  You might be able to do some magic and convert a concatenated value to columns after that as long as you have a fixed/known number of output columns..

I've never seen a way to magically generate two columns from a single statement.

I'm thinking that two case statements will likely be the most efficient method to get what you want.  Any reason you are against it?
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 41853385
well yes, just have your case statement return a 1 or 0
and based on that result use a pipelined function to return the result set

it's really overkill to do it this way ...

create type samplerow as object (yourdate date, yournum number);

create type samplerows is table of samplerow;

create or replace function returnset (i_num in number) return samplerows pipelined as 
  cursor items is 
    select sysdate-1 as yourdate, 10+i_num as yournum from dual;
begin
  for item in items loop 
    pipe row (samplerow(item.yourdate, item.yournum));
  end loop;
  return;
end;
    
         
with samples as (
  select * from table(returnset(case when 'X' = 'Y' then 0 else 1 end)) )  
select * from samples;

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41853633
I need to be able to return multiple values in my SQL case statement.
A SELECT can return multiple values.

Is there a way other than repeating the Case statements?
Show us your actual case statements so we can work from there.
0
 
LVL 13

Expert Comment

by:Nakul Vachhrajani
ID: 41853801
Also, with respect to the topics/area - is your question specific to Oracle or SQL Server?
0
 
LVL 32

Expert Comment

by:awking00
ID: 41853865
If you would provide some sample data for the payment, dates, and other fields in your table(s) along with a description of the comparisons that need to be made, I am sure the experts here can provide solutions for either Oracle or SQL Server.
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 41854624
>>A SELECT can return multiple values.

How can you return more than one value from a single case statement without concatenation of a collection of some type?  Most databases I know return a single object type from a case.
0
 

Author Comment

by:Member_2_7964962
ID: 41854675
I was able to accomplish this task using sub queries, but would like to be able to use something similar to the below.  

Is there a way to include the activity date with the below select, like "pmt.activity_Date".  I am having difficulty including this field since it is using a MAX function.


select

td_pmt.Personid,
td_pmt.Code,
I need to be able to select the activity dte here, but having difficulties because of the MAX function


from rereferecedt rdt,

(select pmt.Personid, pmt.Code, MAX(pmt.activity_Date) AS "Activity Date"
 
 from Person pmt, plan pln
where pln.plan_id = pmt.pln_plan_id
 GROUP BY pmt.Personid) td_pmt,

There are other select statements like the above below
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41854738
Please tell us the database you are using.
Please post sample data and expected results.

Your select appears invalid since you select two columns with the MAX and only have one in the group by.

Either remove code in the select or add it to the group by.
0
 

Author Comment

by:Member_2_7964962
ID: 41855602
I have the second column in the group, but didn't post it when typing the comment.  I was finally able to get my code to work.  

Thanks for everyone's comments.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41855677
Feel free to post your code and accept it as the solution or delete this question.
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41863448
Member_2_7964962, can you explain to us how the selected comment solved your issue?
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Moving away from Access 2003 adp files 4 48
Oracle Partitions. 1 24
What does "Between" mean? 6 48
first parameter x of the decimal (x, y) 1 30
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Viewers will learn how the fundamental information of how to create a table.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question