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?