Categories
Oracle Cloud Fusion Multi Select Parameters Errors Out When two or more items are selected
I'm using Oracle Cloud Fusion to build a data model to feed a report.
I'm trying to create a multi-select parameter. When making the parameter on my data model choose the option selecting all will return NULL. I do this because when I select return all values every option shows up on my report parameters making it unreadable.
So I'm trying to make it so that if null is provided by selecting 'All' it will return all results. Selecting all works fine.
If I select one parameter option the query below works fine.
If I select two or more parameters it always returns ORA-00920: invalid relational operator
SELECT DISTINCT G.CERTIFICATION_NAMEFROM GRC_ACN_CERTIFICATION_VIEW G WHERE G.CERTIFICATION_NAME IN ( CASE WHEN :cert_name_p IS NULL THEN G.CERTIFICATION_NAME WHEN :cert_name_p IS NOT NULL THEN :cert_name_p END )
I have tried to display the results using
SELECT:cert_name_pFROM DUAL
This also errors the same way when I select more than 1.
No matter what I do I can't get it to display or filter two parameter selections.
Best Answer
-
Can you please try the following?
SELECT DISTINCT
G.CERTIFICATION_NAMEFROM
GRC_ACN_CERTIFICATION_VIEW G
WHERE
1=1 AND
(
G.CERTIFICATION_NAME IN (:cert_name_p)
OR LEAST(:cert_name_p) IS NULL
)It is now working for me.
Cheers,
1
Answers
-
Hi @User_9ZFXH,
Can you please try the following to see if it is working for you?
SELECT DISTINCT
G.CERTIFICATION_NAMEFROM
GRC_ACN_CERTIFICATION_VIEW G
WHERE
1=1 AND
G.CERTIFICATION_NAME IN (
(:cert_name_p)
OR LEAST(:cert_name_p) IS NULL
)Hope this help.
Cheers,
0 -
Thank you for responding.
I initially got a missing from error. I moved from to a new line.
I then got an ORA-00907: missing right parenthesis error, but the parenthesis look fine. Not sure what needs changing.
copilot recommended this, but its not the same and results in the same error when I select two options.
0 -
Try this pattern for multiple value parameters
if data set is physical sql data source ApplicationDB_ FSCM/HCM/CRM
select all … where 1=1 and (coalesce(null,:p_batch_name) is null or glb.name in (:p_batch_name))
if data set is logical sql data source Oracle BI EE
select all … where 1=1 and ((case when ('null' in (:p_batch_name)) then 1 end = 1) or ("Journal Batch Details"."Journal Batch Name" in (:p_batch_name)))0 -
0
-
Yay this works. I'm hoping you can help me with one question that extends this situation.
Since 'All' comes through as null when I put the parameters on my layout it shows up blank, but I get all results. I want my parameter to read 'All' in my layout
0 -
NOTE null is 'All' only is you have a lov and you tell it do do so
Yes you can do that either in your data model or in each layout using a condition (if).
In data model for example by adding a column to your data model with a calculation
for example if physical sql where parameter is text (if number or date then cast to character to_char)
select all … , (case when (coalesce(null,:p) is null) then 'All' else :p end) as s_p from …
0 -
How would I add an if statement to my layout?
0 -
If your layout is type RTF ? then in your "Template builder for word" add in menu "BI Publisher" after you have upload your sample.xml use the button "Conditional Format" which will add C EC for the start ad end of an if.
1 -
Thank you!
0
