0

I am trying to create a report that allows the users filter out the products that they want to see by adding a parameter for the Product_No in my Oracle PL/SQL. I am using SQL Server Reporting Services that is connected with the Oracle database to my report.

My challenge is that if a user does not enter any Product_No, then my report should return all the products.

While the Product_No has already in my SELECT clause, I felt like adding some condition in the WHERE clause should work.

But something went wrong with my code, and it returns NULL if I do not enter the Product_No (if I enter a Product_No, then it works):

select Product_No, Product_Name
from Product_Table
where (:Product_No is null) OR
     ((:Product_No is not null) AND Product_No IN (:Product_No))

I simplified my code to make sure I am making sense. Could anyone give me some advice? Appreciated it.

bluebac
  • 59
  • 2
  • 10

3 Answers3

1

After reading this post (How to handle optional parameters in SQL query?), I tested the following code, and it works: 

WHERE Product_No = nvl(:Product_No, Product_No)

Basically, nvl() will return the Product_No if the user defined value is NULL.

However, the performance was not highly optimized I guess, because it checks every row in my table. I'm open to any better ideas...

bluebac
  • 59
  • 2
  • 10
1

you may create a function-based index

create index idx_prod_no on Product_Table (nvl2(Product_No,1,0));

and run statistics package the index to take effect :

exec dbms_stats.gather_table_stats(myschema,'Product_Table',cascade=>true);

and use with this where condition to improve performance :

where nvl2(Product_No,1,0) = nvl2(:Product_No,1,0)

you may test it with including execution plan to show index usage :

SQL>set autotrace on;
SQL>var Product_No number; -- to see the results for NULL values
SQL>select Product_No, Product_Name
     from Product_Table
    where nvl2(Product_No,1,0) = nvl2(:Product_No,1,0);/

SQL>var Product_No number=1; -- to see the results for Product_No = 1 (as an example)
SQL>select Product_No, Product_Name
     from Product_Table
    where nvl2(Product_No,1,0) = nvl2(:Product_No,1,0);/
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
0

I'm not familiar with Oracle but, based on how I would do this with SQL Server I would guess at...

select Product_No, Product_Name
from Product_Table
where (:Product_No is null) OR Product_No IN (:Product_No)
Alan Schofield
  • 19,839
  • 3
  • 22
  • 35