I want to be efficient and pass a null when all values are desired which in most cases mean that there is no filtering in the query.
This won't work:
T1.TYPE_OF_ASSITANCE_CODE IN NVL(:PV_TypeAssitant,T1.TYPE_OF_ASSITANCE_CODE)
You have to use the COALESCE function against a list.
This will work:
( T1.TYPE_OF_ASSITANCE_CODE in (:PV_TypeAssitant) OR
COALESCE(:PV_TypeAssitant, 'Get All') = 'Get All' )
The COALESCE function finds the first non-null value in the list. In the case above, if all values are selected then a null is passed to the parameter PV_TypeAssitant. The COALESCE function finds "Get All" as the first non-null value. It effectively doesn't do any filtering for that value in the query.
My next blog entitled Lexicals, No Triggers and Other Tricks will refine the above example.
If some values are picked then you only want this line as part of the where clause.
T1.TYPE_OF_ASSITANCE_CODE in (:PV_TypeAssitant)
If a null is passed (all values), then you simply want the line of code omitted.
