where clause depends on input

[ecannizzo]

I'm writing a procedure in a package. This procedure takes 3 in parameters:

appno number
userno number
action varchar2

I need to select rows from a table based on these 3 parameters however if the parameter is passed in null I don't want it in my where clause.
I thought of doing something like this:

select * from table1 where
if appno is not null then app_no = appno end if
if userno is not null... and so on

Can someone help me figure out what the best solution for my problem is? Thanks!

 

[oradba101]

Greetings,

How about a series of CASE statements

Regards,



William Chadbourne
Oracle DBA

 

[ecannizzo]

Can you give me an example or point me to a website with an example of how I can use it in my situation?

 

[sem]

1.
select * from table1 where
(appno is null or app_no = appno)
and
(if userno is null or user_no = userno)
...

2.
select * from table1 where
nvl(appno,app_no) = app_no)
and
nvl(userno,user_no) = user_no)
...

But IMHO the best way is to use dynamic sql to make CBO's life a bit easier


Regards, Dima

http://seminihin.narod.ru