Using @prompt in SELECT statement: Flawed SQL Fundamentals

building semantic layer
1

Hello BO Folks,

I want to use @prompt function in the SELECT statement for an object(Both BO designer guide & Forums says so.).
So I typed the SQL in the “SELECT” field in the “Definition” tab of the “Edit properties”
dialog for this object as below:

ABCD_BUSINESS_UNIT.NAME IN @Prompt(‘Enter Name’,‘A’,‘ABCD Business Unit\Name’,Multi,Constrained,Not_Persistent,)
But when i use this object in the Results Objects pane of Webi, I do get the prompt, then after selecting the values I’m getting DB error as following:
“A database error occured. The database error text is: ORA-00923: FROM keyword not found where expected
. (WIS 10901)” :hb:

Here is the SQL generated at Webi/Report side:
SELECT
ABCD_BUSINESS_UNIT.NAME IN @Prompt(‘Enter Name’,‘A’,‘ABCD Business Unit\Name’,Multi,Constrained,Not_Persistent,)
FROM
ABCD_BUSINESS_UNIT
Can you please help me out why @prompt is not working when used in SELECT clause.
Also in SQL world i have never seen a conditional parameter like ‘IN’ or ‘=’ used in the SELECT statement, thats why when I run this SQL statement(replacing @Prompt with some valid value) in SQL developer, I get the same error message. It seems the concept itself is flawed. :blue:
Then why the BO designer guide states that we can use @Prompt in SELECT clause. :crazy_face:

Really confusing. Please provide your expert advice.

PS: I’m using BOXI 3.1 & Oracle 10g

naveenu :india: (BOB member since 2012-07-14)

2

It may help if you think of this in terms of what the generated SQL would look like. If you did get the prompt, and you selected values “A”, "B’, and “C”, the generated SQL would be:

SELECT
ABCD_BUSINESS_UNIT.NAME IN ('A','B','C')
FROM
ABCD_BUSINESS_UNIT

This, of course, is invalid SQL and is the reason for the error.

Are you intending to include the result of the @prompt() in the result set? If so, you can include the @prompt() in the object’s SELECT clause, but without a condition:

@Prompt('Enter Name','A','ABCD Business Unit\Name',Multi,Constrained,Not_Persistent,)

But even this would produce the following:

SELECT
('A','B','C')
FROM
ABCD_BUSINESS_UNIT

which will also fail since there are multiple values. You can only use single-select prompts in the SELECT clause:

@Prompt('Enter Name','A','ABCD Business Unit\Name',Mono,Constrained,Not_Persistent,)

Joe

joepeters :us: (BOB member since 2002-08-29)

3

Bang on target Joe, thanks. The last code did work.
Now I have two questions on this,
(a) Does that mean we cannot use Multi options in our prompt??, if yes,
(b)Then what purpose is the last code doing . i.e how is this SQL working.

SELECT "A"
FROM
  ABCD_BUSINESS_UNIT

Do remember here that “A” is a value from the column Name & not the column itself.

naveenu :india: (BOB member since 2012-07-14)

4

my purpose of using @prompt in SELECT clause is to have filtering on column values.

naveenu :india: (BOB member since 2012-07-14)

5

Hi,

That’s the purpose of the WHERE clause in an SQL statement, and NOT of the SELECT clause.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

6

Are you saying that you want the prompt to produce a dynamically selected column? That’s possible, but it takes a little more work. Consider:

CASE @Prompt('Enter Name','A','ABCD Business Unit\Name',Multi,Constrained,Not_Persistent,)
WHEN 'A" THEN table.a
WHEN 'B' THEN table.b
WHEN 'C' THEN table.c
END

Joe

joepeters :us: (BOB member since 2002-08-29)

7

Absolutely right. Thanks you all for your time & effort. Appreciate it. :smiley:

naveenu :india: (BOB member since 2012-07-14)