#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?
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.
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)
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?