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