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
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
WHERE Location IN('North Chennai', 'South Chennai');
When the same query is used in search screen it may have Three possible Values
- 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).
FROM PERSONS P
WHEN 'P101' IS NULL THEN
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
WHERE nvl(PERSON_ID, '~') IN (SELECT column_value
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
WHEN P_CSV_PERSON_ID IS NULL THEN
END)) IS NULL
OR PERSON_ID IN
For more details on CSV_TO_TABLE refer Link