Click here to Skip to main content
15,506,025 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am new in Oracle. I create a procedure in which i have one select query.
 SELECT
  product.digital_product_id ,
  product.product_type ,
  product.product_description ,

FROM my_product product

WHERE 1 = 1
and UPPER(product.digital_product_id) LIKE  '%'  ||  UPPER(param_productid) || '%'
and UPPER(product.product_type) LIKE        '%'  ||  UPPER(param_producttype) || '%'


input parameters:-
param_productid,param_producttype


i need that when i pass NULL then my query give entire data. or when i pass productid or producttype then filtered data will come. In my table producttype is blank for many rows and i need that data also when i pass productid of bank data.
can any one suggest me how can i do it?

What I have tried:

i tried to add where condition on runtime my concatenate like MS SQL Server but i did not success.
Posted
Updated 26-Aug-22 2:14am

Replace 1 = 1 (which is completely unnecessary) with param_productid IS NULL and use OR instead of AND.
 
Share this answer
 
Comments
TCS54321 26-Aug-22 4:13am    
thanx for your solution but i think you did not got my point. i will explain you.
here is my table sample data:-
digital_product_id product_type
1 --abc
1 --abc
--abc
4 --
4 --xyz
5 --xyz
6 --pqr
6 --

now when user pass product_type=abc then first 3 row will come.
if they pass productid=4 then 4th and 5th row will come.
now can you suggest any solution. i don't think so OR condition work here.
Be consistent in the operators you are using either AND, OR or &&, || but don't mix and match

Based on the example in your comment and the code you have posted I'm presuming that you have two parameters to this code
SQL
:param_productid
:param_producttype
If :param_productid is not null then filter the table on the value contained within it.
If :param_producttype is not null then filter the table on the value contained within it.
If both parameters are null then return the entire table.

If that is the case then this will work
SQL
WHERE ( 
	CASE WHEN :param_productid IS NULL AND :param_producttype IS NULL THEN 1  
	WHEN UPPER(product.digital_product_id) = :param_productid  AND :param_producttype IS NULL THEN 1
	WHEN UPPER(product_type) = :param_producttype AND :param_productid IS NULL THEN 1
	WHEN UPPER(product.digital_product_id) = :param_productid  AND  UPPER(product_type) = :param_producttype  THEN 1
ELSE 0 END) = 1
Caveat - I haven't tested all of the scenarios and I might have the syntax of the parameters incorrect (I tested this in SQL server)

Basically reproducing the 1=1 test, but instead of an explicit 1= working out whether or not it should be 1 or 0
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900