Adding multiple optional parameters to SQl query

0

Hello

I want to create a report using a SQL query; something like this

SELECT * FROM ORDERS WHERE ORDER_TYPE_ID = $P{my_order_type_id} AND ORDER_Company_ID = $P{my_order_company_id}

But my web application needs to be able to restrict or not restrict what users see based on the type of user. So the 'my_order_company_id' parameter may not always be passed for all users.

Does this mean that when 'my_order_company_id' is not passed to the query the report will run the following query a instead of query b?

a) SELECT * FROM ORDERS WHERE ORDER_TYPE_ID = 1 AND ORDER_Company_ID = null

b) SELECT * FROM ORDERS WHERE ORDER_TYPE_ID = 1

If so, is there a way for me to make the report query b?

Thanks

Cris.Abraham's picture
Joined: Nov 21 2017 - 5:37am
Last seen: 2 years 2 weeks ago

Edit: Sorry, posted it as an answer now.

MiditecReportDev - 2 years 2 weeks ago

2 Answers:

0

I think (only thinking as I'm not able to open the studio right now) you can use your SQL (without the parameter that can be null) and do the rest with filter expressions to manually filter out expressions the user is not allowed to see.

 

So basically you get *all* the data where ORDER_TYPE = 1 and say in your filter expression that the user should only be able to see those where the company ID matches his my_order_company_id and that being only the case when my_order_company_id is not null.

MiditecReportDev's picture
Joined: Mar 6 2016 - 11:42pm
Last seen: 1 year 2 months ago
0

You can use the below condition in where clause which will solve your problem,

SELECT * FROM ORDERS WHERE ORDER_TYPE_ID = $P{my_order_type_id} AND ORDER_Company_ID like DECODE($P{my_order_company_id},'','%','0','%',$P{my_order_company_id});

 

 

Shazia's picture
22
Joined: Aug 2 2017 - 6:08am
Last seen: 7 months 3 days ago
Feedback
randomness