IN and NVL Function in Oracle BIP Data model SQL Query.

Here is the sample query where you can use IN clause and NVL Function in the SQL Query - where clause in the Oracle BIP report.

Ex : Say you have an input parameter with the Comma separated values, and the report should pull all the required values as per the input parameter or it should pull all the records when no value is provided.

Input Parameter -   :P_NAME

Properties -   Comma separated values

Sample Query

SELECT * FROM <TABLE>

WHERE ( 1 = DECODE(:P_Name,null,1,0) OR name IN (:P_Name))

This way it will pull all the records if no input parameter is specified or if any input parameter is specified it will retrieve only those records.