Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2016-10-20
12
Medium Priority
?
124 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 41

Expert Comment

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

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
Independent Software Vendors: 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 53

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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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 78

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 78

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 53

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

564 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