Oracle Transactional Business Intelligence

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Oracle Cloud Fusion Multi Select Parameters Errors Out When two or more items are selected

Accepted answer
169
Views
13
Comments

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.

Welcome!

It looks like you're new here. Sign in or register to get started.

Best Answer

«1

Answers

  • Rank 8 - Analytics Strategist

    Oracle Cloud Fusion Multi Select Parameters Errors Out When two or more items are selected

    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,

  • Rank 4 - Community Specialist

    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.

  • Rank 7 - Analytics Coach
    edited Feb 20, 2025 9:58AM

    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)))
    
  • Rank 8 - Analytics Strategist

    Hi @User_9ZFXH,

    Here is a working data model with the above query.

    Hope this help.

    Cheers,

  • Rank 4 - Community Specialist

    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

  • Rank 7 - Analytics Coach
    edited Feb 20, 2025 10:21AM

    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 …
    
  • Rank 4 - Community Specialist

    How would I add an if statement to my layout?

  • Rank 7 - Analytics Coach

    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.

  • Rank 4 - Community Specialist

    Thank you!

Welcome!

It looks like you're new here. Sign in or register to get started.