Solved

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

Posted on 2016-10-20
12
77 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
  • 4
  • 2
  • 2
  • +4
12 Comments
 
LVL 40

Expert Comment

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

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 36

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
 
LVL 45

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 11

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 31

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

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 76

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 76

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 45

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

759 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now