Hello,
I am trying to use NVL in where condition and purpose are if the user is not entering a parameter in the where condition, then with the NVL function, all rows should be returned.
If the column is null, then NVL doesn't return all the rows, only the rows which are having values would be returned. So how can I use NVL or other built-in function which would return all the rows if the user has not entered a parameter value?
Let me explain with an example.
Case 1
SELECT *
FROM products
WHERE prod_id = NVL ( :param, prod_id);
The above query would return all the rows because prod_id does exist in all the rows, see the below-enclosed screenshot.
Case 2
SELECT *
FROM products
WHERE prod_desc = NVL ( :param, prod_desc)
This query would return only 4 rows as PROD_DESC for the fifth row does not exist
How can I get all the rows if the parameter value is empty or not being entered?
Table structure and data.
CREATE TABLE products
(
prod_id NUMBER,
prod_desc VARCHAR2 (32)
);
SET DEFINE OFF;
Insert into PRODUCTS
(PROD_ID, PROD_DESC)
Values
(1002, 'Pens');
Insert into PRODUCTS
(PROD_ID, PROD_DESC)
Values
(2002, 'Inks');
Insert into PRODUCTS
(PROD_ID, PROD_DESC)
Values
(3002, 'Books');
Insert into PRODUCTS
(PROD_ID, PROD_DESC)
Values
(4002, 'Pencils');
Insert into PRODUCTS
(PROD_ID)
Values
(5002);
COMMIT;