CODEWITHSUNDEEP
obieebi-publisher
Nick
Nick

Reputation: 372

Correct Syntax for optional multi-select parameter in BIP

I have a query that was written as a Data Analysis in Oracle OTBI, and I am using it inside of BIP Data Model and I am structuring parameters for the Oracle BI EE logical SQL. The below parameter :P_Item_Number I would like to use as an optional multi-select parameter in the WHERE clause, where the user can either select 1 or more Item numbers, or they can leave it set to All so that it doesnt filter items.

AND ("Main"."Item" IN(IFNULL(:P_Item_Number, null)) or :P_Item_Number IS NULL)

I am getting the following error when trying to validate the statement:

 java.io.IOException: prepare query failed[nQSError: 43113] Message returned from OBIS. [nQSError: 43119]
 Query Failed: [nQSError: 46033] Datatype: 25 is not supported.

The :P_Item_Number parameter is a string parameter setup for Multiple selection:

enter image description here

What do I need to update my syntax to for this to work? Thanks in advance.

Upvotes: 0

Views: 2772

Answers (3)

Nick
Nick

Reputation: 1

The best I've found in this for text parameters is the following:

AND (IFNULL("Main"."Item", 'NULL') IN (:P_Item_Number) OR IFNULL("Main"."Item", 'NULL') IN (CASE WHEN 'null' IN (:P_Item_Number) THEN IFNULL("Main"."Item", 'NULL') END))

By default, OBIEE parameters display a text value of 'null' when the value is blank. This is different from NULL.

I have found some parameters also do better when creating a text value if the value is NULL, hence the IFNULL function. But you can type any word you choose to replace the NULL. It doesn't have to be 'NULL'.

Upvotes: 0

Danimal
Danimal

Reputation: 1

I have had luck using this structure of the Where Statement, substitute your values of course:

WHERE (papf.person_number IN (:personnumber) OR 'All' IN (:personnumber || 'All'))

Upvotes: 0

d r
d r

Reputation: 7846

In your Where clause put something like:

...  ...  ...
WHERE ITEM IN(:P_ITEM_NUMBER)
...  ...  ...

AND
when defining the parameter (P_ITEM_NUMBER:Type:Menu) select the option "All values passed" this means all selected values (could be 1, more then 1 or All)
ADDITION
Here are the test screens as a sample. If you define it all right then if checked left of All - you get all the params listed, if it is not checked but there is 1 or more of the options checked you will get them and if nothing is checked then nothing is going to be selected... enter image description here

Upvotes: 0

Related Questions