asked on

parameter - cond

Hi
Is it possible to have a conditional parameter on a query
eg
--:p_para1 -- enter either 1 or 2
--:p_product
--:p_company

select
t1.*
from mytable t1
where
(if :p_para1 = 1 then t1.product = :p_product
else
t1.company = :p_company)

Regards

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

OR is correct, but needs to be more explicit (I think, not entirely sure on what you're trying to if/else in your pseudocode)

select
t1.*
from mytable t1
where
( :p_para1 = 1 and t1.product = :p_product)
or
(:p_para1 = 2 and t1.company = :p_company)

Open in new window

alternately, but this second version is likely to be less useful with indexes

select
t1.*
from mytable t1
where
( :p_para1 = 1 and t1.product = :p_product)
or
(:p_para1 != 1 and t1.company = :p_company)

Open in new window

PHIL Sawyer

ASKER

Thanks

Sean Stuber

hmm, I was maybe a little too late.

Are you sure the accepted answer does what you wan it to?

Sean Stuber

philipsivyer,

for example - let's say you have row of data like this...

PRODUCT= your_product
COMPANY = your_company

Then I pass in the following parameters:

p_para1 = 1
p_product = my_product
p_company = your_company

I assume you would want to exclude that row. The answer you accepted does NOT, it would return that row which doesn't seem correct