Prompt syntax error

building semantic layer
1

In Designer, I am getting the following error when trying to establish a prompt with an LOV:

 Incorrect syntax near ','.:102

The prompt is as follows:

 @Prompt('01.  Select Name','A','Miscellaneous\Name Combined',multi,free)

The generated SQL in Reporter is as follows:

Name IN (@Prompt(‘01. Select Name’,‘A’,‘Miscellaneous\Name Combined’,multi,free))

In reporter, the LOV is displayed but I get the above error when the query is executed when multiple selections are made.

bender (BOB member since 2003-05-08)

2

#1) I guess you chose multiple values from LOV at your report end.
#2) If the ans for #1 is YES, then the chosen values are being passed with comma seperator.
#3) Did you “Edit SQL” and try to run the same at database end?

Perform the #3 and let me know what do you get.

Good Luck,

  • Arun.

nellaiarun :india: (BOB member since 2003-01-15)

3

I edited the generated SQL (Sybase) to the following:

SELECT Name FROM Name_Table
WHERE Name IN (albemarle,colton,jenkins)

and got a different parsing error that indicated the names in the list are invalid column names. So I surrounded each name with doulbe quotes (") and it passed the parse. Then I took the acceptable SQL and ran it in Sybase and it worked fine.

bender (BOB member since 2003-05-08)

4

I also tried the following:

In SQL Viewer in Reporter I changed the query to the following:

SELECT Name
FROM Name_Table
WHERE Name IN (@Prompt(‘Select Name’,‘A’,{‘Albemarle’,‘Coulton’,‘Jenkins’},multi,free))

and got the same syntax error.

Syntax error is:

Parse failed: Incorrect syntax near ‘,’.

bender (BOB member since 2003-05-08)

5

Can you post the whole SQL query generated in the SQL Viewer of Reporter?

-Mohan

cpmohanraj :australia: (BOB member since 2002-09-23)

6

Original query viewed in SQL Viewer:

SELECT
Name
FROM Name_Table
WHERE
Name IN (@Prompt(‘Select Name’,‘A’,‘Miscellaneous\Name Combined’,multi,free))

This query failed with "Incorrect syntax near ‘,’.

I changed “multi” to “mono” and it passed.

Then I changed query to the following in SQL Viewer:

SELECT
Name
FROM Name_Table
WHERE
Name IN (@Prompt(‘Select Name’,‘A’,{‘albemarle’,‘coulter’,‘jenkins’},multi,free))

This query failed with "Incorrect syntax near ‘,’.

Just as a Lark, I went to a different Universe and created a very simple Prompt object:

@Prompt(‘Enter’,‘A’,{‘A’,‘B’,‘C’},multi,free)

This object failed with:

“Parse failed: Incorrect syntax near ‘,’.”

bender (BOB member since 2003-05-08)

7

Are you creating an object with just the @Prompt() or you are creating some sort of condition object :?:

Sri

Sridharan :india: (BOB member since 2002-11-08)

8

Which database you are using??

cpmohanraj :australia: (BOB member since 2002-09-23)

9

Using Sybase

bender (BOB member since 2003-05-08)

10

The object contains only the @Prompt which references a LOV that was created with freehand sql. The freehand sql contains a UNION of two tables.

When the @Prompt object is used in the report as:

Object in list @Prompt

the LOV is displayed as expected. Multiple selections can be made from the LOV. However, when the query is executed, the syntax error appears.

bender (BOB member since 2003-05-08)

11

Just throwing out some ideas:’

Do you also get the syntax error when you TYPE in multiple values for the prompt (instead of selecting from the LOV)?

Could it be that somehow BO generates a SQL enclosing the values from the prompt in single quotes instead of using double-quotes? I know Oracle uses single quotes.

Try:

SELECT Name 
FROM Name_Table 
WHERE Name IN @Prompt('Select Name','A',{'albemarle','coulter','jenkins'},multi,free)

Or

SELECT Name 
FROM Name_Table 
WHERE Name IN @Prompt('Select Name','A',,multi,free)

Change your prompt to:

(@Prompt('01) Select Name','A','Miscellaneous\Name Combined',multi,free))

Andreas :de: (BOB member since 2002-06-20)

12

Okay, Guys and Gals, here’s the skinny!

Andreas’ suggestion to remove the outer parens around the @Prompt clause solved the problem. Why this worked is a mystery to me. I say that because, in the report I am working on, there are several similar @Prompt’s but only this one causes the problem. And the outer parens were inserted only for this @Prompt, not the others. The only difference in other @Prompts is that they are all “mono”.

I was thinking that the generated SQL was not kosher for Sybase so I attempted, for the first time, to use Business Tracker to see the SQL after it was coverted. Well, I got BT working but what it generated did not give me a clue. I could not relate anything I saw to the Select statement I thought was being created. Someday, I will have to get more familiar with the Business Tracker product.

Anyway, Andreas, thank you for your assistance. Can I buy you a beer or two?

bender (BOB member since 2003-05-08)

13

I encountered the same problem time ago (going against Oracle)… I am glad you were able to fix it.

Maybe, someone with access to BO v6 and Oracle or Sybase can try to re-create this scenario and see if this bug has been fixed?

Andreas :de: (BOB member since 2002-06-20)

14

Update:
See Resolution Entry 9587 on www.techsupport.BusinessObjects.com (requires valid maintenance contract)

Andreas :de: (BOB member since 2002-06-20)

15

Andreas,

I am facing a similar issue. How do I access this Resolution Entry 9587 on SAP Support Portal? Can you please help?

The Boss :india: (BOB member since 2008-02-08)

16

I am sorry Boss, my post you are refering to is from 5 years ago…
I kindly suggest you contact SAP techsupport directly.

Andreas :de: (BOB member since 2002-06-20)

17

It is Ok Andreas, Thank you for the response. I will look into the support portal.

The Boss :india: (BOB member since 2008-02-08)