Solved

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

Posted on 2016-10-20
12
109 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 38

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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
LVL 51

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 14

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 51

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to recover a database from a user managed backup

624 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