Problem using Multi in Prompt in Freehand SQL

system · January 30, 2004, 9:44pm

Hi,
I see a number of posts related to things that you can’t do in prompts for lovs with Freehand sql but I don’t see this as one of them …

A user has a prompt in the following format:
TC6_MT_INV_IND IN (@Prompt(‘MT Funds:’,‘A’,{‘Y’,‘N’,’ '},multi,) )

When the query is run and the Y and N chosen from the Values list, they are placed in the values response box with a semi colon between them for some reason. Then when the query is run, it fails with an sql error which says [IBM][CLI Driver][DB2] SQL0104N An unexpected token “,” was found following “”. Expected tokens may include: "+ ) - ".

Is there something wrong with the prompt format? The behavior of various tests suggests that a multi-valued response won’t work when the value is alpha while we have had no such problem with numeric values.
Thanks!

jkeith (BOB member since 2002-08-20)

system · January 31, 2004, 7:47pm

This kinda makes sense to me. Business Objects always places a semicolon between values in the prompt box, but then translates that to commas in the SQL. However, if you’re using freehand SQL, it probably doesn’t have the chance to do the conversion.

As a workaround, how about building the prompt so they can select a value or enter * for both. Check here for ideas on how to do that.

MichaelWelter (BOB member since 2002-08-08)