This one sat in my “to do” box for a while before I had a chance to review it. I think I can share some insight after having some time to think / experiment with it.
In a message dated 00-03-07 14:52:27 EST, you write:
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.
[ snip ]
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”
I actually get a different error. I am testing against a MS Access (or Oracle) , however, so the syntax error would be different.
I like the idea of using a prompt in Free Hand SQL to generate a prompt. I can certainly see how it would work for a single value. I also can see why it would not work for multiple values, I think. 
Keep in mind that the prompt function is preprocessed by BusinessObjects. Meaning that it does not pass through with the SQL. BusObj must translate the @Prompt() language into regular SQL.
If you use MONO in your @prompt, then you are expecting a single value. For regular SQL, this would translate into ‘X’ (or X with no quotes for numeric values). This value is typically used in a where clause (prompt), but could be used in a select.
For a single value, BusObj is expecting a single value comparison. Something like equals. For example, a where clause:
select
from
where table.column = @Prompt(‘Select a value’,‘A’,{‘A’,‘B’,‘C’},MONO,)
would become:
select
from
where table.column = ‘A’
This syntax would work equally well in the select clause. However, using MULTI in an @Prompt implies a multiple selection, and that is expected to be used only in an IN clause. In other words:
select
from
where table.column IN @Prompt(‘Select a value’,‘A’,{‘A’,‘B’,‘C’},MONO,)
would become:
select
from
where table.column IN (‘A’,‘B’,‘C’)
This syntax is fine. But try putting the same thing in a Select clause, as in:
select @Prompt(‘Select a value’,‘A’,{‘A’,‘B’,‘C’},MONO,) from
would become:
select (‘A’,‘B’,‘C’)
from
I think that the parens in the select clause are causing the problem. The error that I get when trying this trick against Access is:
[Microsoft][ODBC Microsoft Access 97 Driver] Syntax error (comma) in query expression ‘(‘A’,‘B’)’.-3100
Notice the parens around the two values I selected? (‘A’,‘B’)
Trying the same trick against Oracle returns the error:
ORA-00907: missing right parenthesis :-907
Interestingly enough, I get the same syntax error in Oracle if I try to use the SQL:
Select (‘A’,‘B’)
from
But the following:
Select (‘A’), (‘B’)
from
… actually parses correctly! So, identifying that the problem is that there are extra parens when using a MULTI in the @Prompt, is there a solution? The idea would be to pass in the syntax that is expected in a select clause. That would be:
select ‘A’,‘B’,‘C’
from
Since I don’t see any way to remove the parens without doctoring the SQL code, and since there isn’t any way (that I know of) to doctor the SQL code after the @Prompt has been evaluated, I think you may be out of luck. Sorry.
But at least it is nice to have an understanding of why it won’t work, instead of worrying if there is something that is wrong with the code. At least if my assumptions are correct.
Regards,
Dave Rathbun
Integra Solutions
www.islink.com
Listserv Archives (BOB member since 2002-06-25)