Using @prompt in SELECT statement: Flawed SQL Fundamentals

[Former Member]

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)"

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.

Then why the BO designer guide states that we can use @Prompt in SELECT clause. Really confusing. Please provide your expert advice.

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

[Former Member · Accepted solution]

Hi Naveen,

It works for me in BO XI 3.1 SP3 with Oracle 11g, I tested it for mono-single value.

See atached.

Thanks,

Prathamesh

[Former Member · Accepted solution]

Thanks Prathamesh. Yes for Mono, its working but for Multi it fails.

But even with Mono, what purpose is this @prompt at the SELECT clause is serving.

When, it runs it will have the same column value across the report; see this SQL:

SELECT "A" FROM   ABCD_BUSINESS_UNIT ;

Here "A" being the column value that got select by prompt.

Please shed more light into it.

[Former Member]

By the way we are using BO XI 3.1 SP3 FP 3.5

[Henry_Banks]

Hi,

What's your Support Package and Fix Pack level?

Have you checked the latest 'Fixed Issues' lists (pdf) to see if bugs related to this problem synopsis are fixed in the higher SP04/SP05 and FixPack4.x/5.x ?

Regards,

H

[Former Member]

Hello Henry,

Whats the use of checking the 'Fixed Issues', if the concept of 'Multi' in @prompt's SELECT clausecan't be used.

Do you mean to say, in latest package/version this will run?

[Henry_Banks]

it's called 'basic fact-finding' - You might find that searching the list of 'known issues' can help you identify a fix (for a bug defect) which may be of benefit .

In your case, you should read the SP04 and SP05 ones, and also the Patchx.x release note.

You could also try the solution search for knowledge base articles (notes) www.service.sap.com/notes

regards,

H