Solved

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

Posted on 2016-10-20
12
97 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 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 48

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 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 48

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

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.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

792 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