Incorrect syntax near ','. :102

building semantic layer
1

Hi,

When I select multiple values from an @Prompt I get the following Business Objects SQL sentence error:

Incorrect syntax near ‘,’. :102

When I select only one value from the same @Prompt, the query runs fine.

Anyone have any ideas?

I have searched the archives and realize that multiple values must be separated by semi-colons.
In fact, BO takes care of that automatically…meaning I see my multiple values being placed in the prompt separated by semi-colons. However, as I said above, I get a SQL error.

Thank You,
Guy Johnson

Listserv Archives (BOB member since 2002-06-25)

2

There is a bug in Business Objects 4.x (I can proudly say that I found it) where when you refresh a report by using the @Prompt in your script you will have this problem.
Situation: using the InList in your report and the @prompt in the script where you pass through only one variable in the first instance of your @prompt. Solution: always make sure that during the first instance of a report being refreshed you pass through at least two variables. If your @prompt is in a loop then you can use only one variable on the second pass. Alternate solution: if you only ever have to pass through one variable then make sure that you are using the “equal to” operator rather than the “inlist” operator.
Simon

Listserv Archives (BOB member since 2002-06-25)

3

In a message dated 00-03-07 13:00:03 EST, you write:

When I select multiple values from an @Prompt I get the following Business
Objects SQL sentence error:

Incorrect syntax near ‘,’. :102

When I select only one value from the same @Prompt, the query runs fine.

Anyone have any ideas?

I’m guessing here, but was this prompt built in the universe? I suspect that it was…

If that is the case, make sure that the prompt was built with the IN clause, not the equal sign =. The object would parse in Designer either way, but the fact that your prompt works for a single value and not for multiple is a symptom that points to an error in the object SQL.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

4

Simon and Dave,

Thank you for your quick responses. I’ve been monitoring the list for some time and have found
your knowledge to be very helpful.

In this particular case though, I have left out some background info.

The prompt is one I created on the Report side, not the Universe side. I am using Free-hand SQL as a data source. An example would be:

SELECT @PROMPT(‘CHOOSE’,‘A’,{‘A’,‘B’,‘C’},MULTI,CONSTRAINED);

Notice I am supplying my own LOV in brackets, instead of specifying a class\object.

Incidentally, the reason I use the above syntax is so that I can re-use the value(s) chosen
for a stored procedure call elsewhere in the report. This method has served me well for
other reports where only one value can be chosen. In other words, MONO instead of MULTI.

If you have the inclination, copy the above SELECT clause in a Free Hand SQL window, and then run it.
I would be curious if you get the same results. Namely, choosing one value works fine,
choosing more than one values yields the following error:

“Incorrect syntax near ‘,’. :102”

Other environment info:
BusinessObjects 5.0.1
Sybase 11.x

Thank you again for your responses.
Guy Johnson

Listserv Archives (BOB member since 2002-06-25)

5

Thank you for the compliment.
I took a look at what you did and had the same problem myself. If you change the “Constrained” to “Free” and change the “;” to a “,” then you will not have this problem, but of course that is a lousy solution. Problem is that using the @prompt with a fixed list of values forces the delimeter to be a “;” when you want a “,”. I am surprised that I have not come across this myself yet as I use free-hand SQL all the time.
Sounds like you found a new bug!
Simon

Listserv Archives (BOB member since 2002-06-25)

6

Guy,
In the conditions make sure that you are using “in list” not “equal to”.

Jim Lafferty

Listserv Archives (BOB member since 2002-06-25)