0

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.

Nick
  • 268
  • 8
  • 33

2 Answers2

0

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

d r
  • 3,848
  • 2
  • 4
  • 15
  • Hi, yes I've done that but as I said above this is intended to be an optional multi-select parameter, so if the parameter is left as 'All' it gets passed as a Null and causes no data to be returned from the query. This is why I was using the OR operator, but it still does not work as indended. – Nick Dec 16 '22 at 12:57
  • Check your cashing - this could cause confusion.... When All is checked (though the options are not) then all values should be passed. You can force initial selection (checked All) if you check "Refresh other parameters on change" - it doesn't matter if there are any... – d r Dec 16 '22 at 15:55
  • I believe we were told by our Oracle consultant that you are limited to how many values you can pass with leaving the "All Valued Passed" option selected. I want to say it was somewhere up to 1,000 values that can be passed as an array. The parameter we are using for item number has several thousand values so I believe thats why we can't use the "All Values Passed" option, and why I was trying to use OR with the NULL value passed. I wish Oracle had a more native way of doing this. If you can think of any other ways of coding the query to handle this I would appreciate it. – Nick Dec 17 '22 at 16:40
  • Sorry, but if NULL Value Passed then it may work for All selected (which is same as none selected) and if just one is selected. If I try to select two options then it fails with error. On the other hand I don't see the sense in offering thousends of options. It is too much to find just two or three of interest and even worse if someone would like to select 300 options ... – d r Dec 17 '22 at 20:15
0

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'))

Danimal
  • 1