Oracle Analytics Cloud and Server

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

regexp_substr function in OTBI with variable

Received Response
177
Views
1
Comments
Rank 3 - Community Apprentice

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:

PROMPT.JPG

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?

Answers

Welcome!

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