When the Column value is NULL it would be replaced by ‘~’ in output<\/p>\n
\r\n SELECT NVL(FAX_NO, '~') FROM DUAL;\r\n SELECT NVL(NULL, '~') FROM DUAL;\r\n<\/pre>\nThe 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.<\/p>\n
\r\n SELECT Name, Age, PhoneNo\r\n FROM Person \r\n WHERE Location = p_location and \r\n Area = NVL(p_area, Area) and \r\n PinCode = NVL(p_pin_code, PinCode);\r\n<\/pre>\nHaving more than one input in where clause
\ni.e<\/em><\/p>\n\r\n SELECT * \r\n FROM Person \r\n WHERE Location IN('North Chennai', 'South Chennai');\r\n<\/pre>\nWhen the same query is used in search screen it may have Three possible Values<\/p>\n
\n
- NULL<\/li>\n
- Single Value<\/li>\n
- Multiple Value<\/li>\n<\/ul>\n
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.<\/p>\n
The below is a simple query which works when PERSON_ID is NULL, Single Value, Multiple Value(CSV).<\/p>\n
\r\n SELECT PERSON_ID\r\n FROM PERSONS P\r\n WHERE ((CASE\r\n WHEN 'P101' IS NULL THEN\r\n NULL\r\n ELSE\r\n 'P101'\r\n END) IS NULL OR P.WATERFALL_ID IN ('P101'));\r\n<\/pre>\nFor Multiple Value we need to do slight modification.We need to convert the CSV values into table and give it as input.<\/p>\n
\r\n SELECT DISTINCT PERSON_ID\r\n FROM tblPerson \r\n WHERE nvl(PERSON_ID, '~') IN (SELECT column_value\r\n FROM TABLE(PACKAGE_NAME.CSV_TO_TABLE(P_CSV_PERSON_ID))\r\n UNION ALL\r\n SELECT '~'\r\n FROM dual);\r\n<\/pre>\nThe select query in the where clause will take the value of P_CSV_PERSON_ID<\/strong> (Single or Multiple value) else it will take ~<\/strong> in case it is NULL<\/p>\n
The Other workaround to this is as below<\/p>\n
\r\nSELECT PERSON_ID\r\n FROM tblPerson \r\n WHERE (((CASE\r\n WHEN P_CSV_PERSON_ID IS NULL THEN\r\n NULL\r\n ELSE\r\n P_CSV_PERSON_ID\r\n END)) IS NULL\r\n OR PERSON_ID IN\r\n (SELECT *\r\n FROM TABLE(PACKAGE_NAME.CSV_TO_TABLE(P_CSV_PERSON_ID))));\r\n<\/pre>\nwhere <\/p>\n
\r\n P_CSV_PERSON_ID= 'P101,P102'\r\n<\/pre>\nFor more details on CSV_TO_TABLE<\/strong> refer Link<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
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… Continue reading