I have a direct SQL analysis in OBIEE that uses presentation variables to filter based on Site Name and Unit Name.

The problem is that in OBIEE if the user selects more than one unit (which I want them to be able to do) it outputs the selected units as a comma delimited string 'UNIT 1,UNIT 2,UNIT 3'. This is an issue because to then use these values in a SQL filter causes an error as IN expects the strings to be comma separated strings 'UNIT 1', 'UNIT 2', 'UNIT 3'

I got this working by using regexp_replace

AND UNIT_LOCATION_NAME in (select regexp_substr('@{PROMPT_UNIT_NAME}', '[^,] ', 1, level) as location from dual connect by level <= length(regexp_replace('@{PROMPT_UNIT_NAME}', '[^,] '))   1)

The filtering works fine with this however the default value for PROMPT_UNIT_NAME in OBIEE is '(All Column Values)' which means that the analysis returns no results when attempting to query an entire site.

I have attempted to resolve this by handling the default value which is where things have come undone.

AND decode('@{PROMPT_UNIT_NAME}', '(All Column Values)', 1=1, UNIT_LOCATION_NAME in (select regexp_substr('@{PROMPT_UNIT_NAME}', '[^,] ', 1, level) AS location FROM dual CONNECT BY level <= length(regexp_replace('@{PROMPT_UNIT_NAME}', '[^,] '))   1))

This throws an error ORA-00907: missing right parenthesis which is not the case. There is no missing parenthesis. Here is the full query:

SELECT
    locd.REGION_LOCATION_NAME
    ,locd.SITE_LOCATION_NAME
    ,locd.UNIT_LOCATION_NAME
    ,oi.*
FROM
    cte_oi_v2 oi
    INNER JOIN LOCATION_D locd on locd.LOCATION_ID = oi.UNIT_LOCATION_ID
WHERE
    1=1
    AND SITE_LOCATION_NAME in ('@{PROMPT_SITE_NAME}')
    AND decode('@{PROMPT_UNIT_NAME}', '(All Column Values)', 1=1, UNIT_LOCATION_NAME in (SELECT regexp_substr('@{PROMPT_UNIT_NAME}', '[^,] ', 1, level) AS location FROM dual CONNECT BY level <= length(regexp_replace('@{PROMPT_UNIT_NAME}', '[^,] '))   1))

Any ideas on what's causing the error or better alternative approaches would be most welcome.

CodePudding user response：

This throws an error ORA-00907: missing right parenthesis which is not the case.

It is the case because DECODE does not work like that as you need to return a single value and not an expression to evaluate so you cannot return 1=1 as one of DECODE's arguments and would need to return a single value 1.

Rather than trying use DECODE and split the string, you should be able to match the values on sub-strings if you include the surrounding delimiters and use OR to alternatively match the default value:

SELECT locd.REGION_LOCATION_NAME
     , locd.SITE_LOCATION_NAME
     , locd.UNIT_LOCATION_NAME
     , oi.*
FROM   cte_oi_v2 oi
       INNER JOIN LOCATION_D locd
       on locd.LOCATION_ID = oi.UNIT_LOCATION_ID
WHERE  SITE_LOCATION_NAME IN ('@{PROMPT_SITE_NAME}')
AND    (  '@{PROMPT_UNIT_NAME}' = '(All Column Values)'
       OR ',' || '@{PROMPT_UNIT_NAME}' || ',' LIKE  '%,' || UNIT_LOCATION_NAME || ',%'
       )

Note: you may be able to rewrite ',' || '@{PROMPT_UNIT_NAME}' || ',' as ',@{PROMPT_UNIT_NAME},'.