I know this topic has been disscussed here is my issue:
I want to prompt my end-user only once but, I want to give them the option to select ‘ALL of the Branches’ or Just one or 2 of the branches.
The only code I found that worked was the following;
However this gives the end user the question 2 times. It makes sense with the OR statement
(@Select(Class\Object) IN @Prompt(‘Select Branch or ALL’,‘a’,Class\Object’,multi,free) )OR(‘ALL’ in @Prompt(‘Select Branch Group or enter * for all groups’,‘a’,‘class/object’,multi,free))
Not only will your example prompt twice, the ALL does not work as stated.
Try:
(@Select(Class\Object) IN @Prompt('Select Branch Group or enter * for all groups','a',Class\Object',multi,free) )
OR
('*' in @Prompt('Select Branch Group or enter * for all groups','a','class/object',multi,free))
The Prompt needs to be the same for both comparisons, otherwise you get the double prompt… and your comparison needs to actually compare using the right value.
When I try the above condition with a multi keyowrd in an object using the @prompt function it will only work when either one item is selected or I use the catch all, if I select more than one item from the prompt values it errors.
In fact any @prompt function using the multi keyword in a Universe object (within Business Objects 6.5 Designer), gives the standard oracle error ORA-00907 - missing right parenthisis.
Looking at what it puts in the prompt box, a list of values delimited by a semicolon, I presume the problem is that the semicolon messes up the generated SQL, thinking the statement has ended prematurely.
I have tried replace and other functions around the @prompt function but these still give the same error. Does anyone know of any way of altering the @prompt delimiter or fooling the SQL into treating it as a valid input, after all using a multiple prompt in a data-provider works fine.
Probably not. The SQL generator is smart enough to translate the semi-colon delimited string from the interface into the proper syntax for whatever “flavor” of data base you are using … usually commas as the delimiter and apostrophes around the individual character values.
What I suspect more is the use if the IN operator. Multi only works with IN, and MONO only works with EQUALS.
Thats what the support from our software vendor initially pointed to, but the error is shown when used with any IN construction which I would normally expect to handle multiple values:
e.g. A where condition on an object contains:
“Team Names”.TEAM IN (@Select(BCA Prompts\BCA Team Prompt)) OR (“Team Names”.TYPE IN (@Select(BCA Prompts\BCA Team Prompt)))
Where the @Select(BCA Prompts\BCA Team Prompt) is:
@Prompt(‘Please enter Team’,‘A’,‘BCA Prompts\BCA Team Names’,MULTI,Free)
NB “Team Names” is a Dervived table with the team name and a team type, and as you can see the I want to be able to either input one or more teams or the catch all of type from the LOV.
I am thus still at a loss to know if it is a bug in the particular versions of BO or the Oracle 9i as I cannot see from the documnetation why it will not work.
Try to get rid of the mixed case table names and the quotes. I’ve seen that cause problems more than once. Also try without using @Prompt() nested inside of @Select() and see if that helps.
No luck using your suggestions. It still errors without the select shortcuts, when using the raw tables rather than the derived tables, and with single case table names.
The quotes in the example represent the BO derived table syntax, in the generated SQL it is actually written out in full form. Using the raw tables this turn out the same SQL.
When you edit the object there is a checkbox that is marked “Show SQL” that will cause the @Select() references to be filled in. Can you do that, and then post the final SQL?
Also, you don’t need the ( ) around the prompt, as it will be filled in for you.
(BOB member since 2002-08-15)
(BOB member since 2007-10-09)
(BOB member since 2002-09-19)
Dom
(BOB member since 2007-10-09)
