When the Column value is NULL it would be replaced by ‘~’ in output
SELECT NVL(FAX_NO, '~') FROM DUAL;
SELECT NVL(NULL, '~') FROM DUAL;
The Below query can be used in scenarios where 3 drop downs are used and Location is mandatory and loaded first based on which Area and Pincode should be populated and selection can be made based on that later.
SELECT Name, Age, PhoneNo
FROM Person
WHERE Location = p_location and
Area = NVL(p_area, Area) and
PinCode = NVL(p_pin_code, PinCode);
Having more than one input in where clause
i.e
SELECT *
FROM Person
WHERE Location IN('North Chennai', 'South Chennai');
When the same query is used in search screen it may have Three possible Values
- NULL
- Single Value
- Multiple Value
The above query works for Single and Multiple value but does not work for NULL.The Above query works for NULL and Single Value but not for Multiple Value.
The below is a simple query which works when PERSON_ID is NULL, Single Value, Multiple Value(CSV).
SELECT PERSON_ID
FROM PERSONS P
WHERE ((CASE
WHEN 'P101' IS NULL THEN
NULL
ELSE
'P101'
END) IS NULL OR P.WATERFALL_ID IN ('P101'));
For Multiple Value we need to do slight modification.We need to convert the CSV values into table and give it as input.
SELECT DISTINCT PERSON_ID
FROM tblPerson
WHERE nvl(PERSON_ID, '~') IN (SELECT column_value
FROM TABLE(PACKAGE_NAME.CSV_TO_TABLE(P_CSV_PERSON_ID))
UNION ALL
SELECT '~'
FROM dual);
The select query in the where clause will take the value of P_CSV_PERSON_ID (Single or Multiple value) else it will take ~ in case it is NULL
The Other workaround to this is as below
SELECT PERSON_ID
FROM tblPerson
WHERE (((CASE
WHEN P_CSV_PERSON_ID IS NULL THEN
NULL
ELSE
P_CSV_PERSON_ID
END)) IS NULL
OR PERSON_ID IN
(SELECT *
FROM TABLE(PACKAGE_NAME.CSV_TO_TABLE(P_CSV_PERSON_ID))));
where
P_CSV_PERSON_ID= 'P101,P102'
For more details on CSV_TO_TABLE refer Link