regexp_substr function in OTBI with variable

3612030

Hello,

I was trying to follow the instructions for my case here:

https://blogs.oracle.com/aramamoo/how-to-split-comma-separated-string-and-pass-to-in-clause-of-select-statement

I have almost the same case but instead of a ('SMITH,ALLEN,WARD,JONES') i have names of employees passed into a variable '@{OWNER}' which in the end gives the same results.

Now, I want to apply this to my prompt (I chose SQL Results as a choice list values) to populate there a list of my selected employees from a variable so my sql will looks like this:

   select "Sales - CRM Customer Overview"."Customer"."Account Owner Name" from "Sales - CRM Pipeline" where "Sales - CRM Customer Overview"."Customer"."Account Owner Name" in (

select regexp_substr('@{OWNER}','[^,]+', 1, level) from dual

connect by regexp_substr('@{OWNER}', '[^,]+', 1, level) is not null )

Image:

But unfortunately then I got an error "Nonexistent table: "dual". (HY000)" and even if I replace the dual with any subject area i get: "Nonexistent table: "Sales - CRM Pipeline". (HY000)"

Can this sql code be used on Subject Areas? If so, then how? How can I achieve values from my variable to not look like ('SMITH,ALLEN,WARD,JONES') but ('SMITH','ALLEN','WARD','JONES') and the be populated in my prompt list values?

Robert Angel

Is this what you are looking for =>

Vishal's blog: OBIEE 11G: Setting multiple values to Presentation Variables through goURL and conditional filtering