Using case to select multiple rows in where statement 1

[lak201]

hi all,
please forgive me if this question is too naive. heres what i am trying to do:
i have a stored procedure that takes in an optional argument which defaults to null. if its null i have to return all the values from a table, else return only those values that have a value match against the given optional argument. so it will be something like this

opt_arg=null --The optional parameter

select * from table
where id in (select case when opt_arg is null
(select id from table)
else opt_arg)

the error i get here is:
"cannot return multiple values in a sub-query"

please note that i cannot write this as

if ( opt_arg is null)
select * from table
else
select * from table where id=opt_arg

because i take actually take in 5 optional arguments which can all be null, and cannot write all the permutations of these arguments.

thanks in advance.

lak.

 

[PHV]

Have you tried something like this ?
select * from table
where id in (select case when opt_arg is null id
else opt_arg end)


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

And this ?
select * from table
where id = opt_arg or opt_arg is null


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rac2]

select * from table
where id in (
               select
                case when opt_arg is null THEN id
                     else opt_arg
                    END
               FROM table
              )

If opt_arg is null then the row is selected because
table.id is always equal to table.id, a tautology.

Otherwise the id must equal opt_arg.

However the subquery always yields a list of values. The number of items on the list is equal to the number of rows in table. When opt_arg is null, the whole list of id values is generated; when opt_arg is not null a list of values all equal to opr_arg is generated. So that is not too efficient.

Try this approach.

SELECT * FROM table
WHERE (id = opt_arg OR opt_arg IS NULL)

Here if opt_arg is not null then the id must equal opt_arg for a row to be retrieved; if opt_arg is null then all rows are retreived. Addtional conditions are combined with AND.

SELECT * FROM table
WHERE
     (id1 = opt_arg1 OR opt_arg1 IS NULL)
  AND
     (id2 = opt_arg2 OR opt_arg2 IS NULL)

 

[lak201]

thanks a lot to both phv and rac2. both your suggestions work fine.

rac2,i have ana additional question for you. does it matter if i write
(id1 = opt_arg1 OR opt_arg1 IS NULL)
vs.
(opt_arg1 IS NULL OR id1 = opt_arg1)

i.e., is it better if i check for the null first, in terms of the execution of the code and performace reasons.

thanks gaina to both of you guys.

lak

 

[rac2]

Most RDBMS have a process of optimizing the work specified in a query. This sort of issue would be handled by the optimizer, it need not worry us. In any case it would be a matter of microseconds unless maybe you were searching government databases. In principle you are correct, first checking that x is null is more efficient.

I like to put the x is null check first for readability. Sometimes the criterion condition is more complex, you read through it, figure out the intention, then discover it is optional and does not apply to the problem at hand. Looking back at my post, I see I did it backwards. Oh well.