[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

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
0
PHIL Sawyer
Asked:
PHIL Sawyer
  • 3
1 Solution
 
slightwv (䄆 Netminder) Commented:
Maybe an OR?

(:p_para1 = 1 and t1.product = :p_product)
or
(t1.company = :p_company)
0
 
sdstuberCommented:
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

0
 
PHIL SawyerAuthor Commented:
Thanks
0
 
sdstuberCommented:
hmm, I was maybe a little too late.

Are you sure the accepted answer does what you wan it to?
0
 
sdstuberCommented:
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
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now