How to return multiple values using case statement in oracle.

Question

Hi,

I want to return multiple values from a query in oracle. For ex:

select count(*)
from tablename a 
where asofdate='10-nov-2009'
and a.FILENAME in (case 
    when 1 = 1 then (select distinct filename from tablename 
        where asofdate='10-nov-2009' and isin is null) 
    else null
end);

I am getting error: ora 01427 single row subquery returns more than one row

Please advice.

Thanks, Deepak

Answer 1

Run this query:

select distinct filename from tablename 
where asofdate='10-nov-2009' and isin is null

You'll see that it returns more than a single row which causes the ORA-01427.

Answer 2

A CASE statement cannot return more than one value, it is a function working on one value.

It is not required for your statement, this statement should work:

select count(*) 
from tablename a 
where asofdate='10-nov-2009' 
and a.FILENAME in (select distinct filename 
    from tablename 
    where asofdate='10-nov-2009'
    and isin is null);

Maybe you have another usage scenario in mind? Something like this: Select * From aTable Where in CASE WHEN Then WHEN Then ELSE END

Then using CASE may not be the right scenario. Maybe this helps you in the right direction:

Select *
From aTable
Where <Case1> and  column1 in <Subselect1>
Or <Case2> and column1 in <Subselect2>
OR Not (<Case1> Or <Case2>) and column1 in <Subselect3>

But this will probably be quite some work for the optimizer ...

Answer 3

The distinct in your Case statement is attempting to return multiple values when only one is allowed, and your SELECT statement will only return one value in one row currently. If you're trying to get the count of each filename, do

SELECT FileName, Count(*)
FROM tablename
WHERE asofdate='10-nov-2009' and isin is null
GROUP BY FileName

Answer 4

For all I can tell, you're looking for something like:

select a.filename, count(*)
from tablename a
where a.asofdate = '10-nov-2009' 
and exists (
    select *
    from tablename b
    where b.isin is null
    and a.asofdate = '10-nov-2009' 
    and a.filename = b.filename
)
group by a.filename

This would find the count of filenames for a day, for which there exists at least one row where isin is null.

If you edit your question and add an explanation of what you're looking for, you might get better answers.