When using a prompt (@Variable command) with an In List operator, you are able to separate multiple values with semi-colons. However, when we create a free-hand SQL query using the @Variable command, the semi-colon does not seem to behave the same way. It seems as if the only values that is considered is the first one (to the left of the first semi-colon). Does anyone know what we are doing wrong or if it is even possible to prompt for multiple values using free-hand SQL?
Thanks,
Peter Borjestedt g
When using a prompt (@Variable command) with an In List operator, you are
able to separate multiple values with semi-colons. However, when we create
a free-hand SQL query using the @Variable command, the semi-colon does not
seem to behave the same way. It seems as if the only values that is considered is the first one (to the left of the first semi-colon). Does
anyone know what we are doing wrong or if it is even possible to prompt for
multiple values using free-hand SQL?
Peter,
Use @prompt with the multi parameter -e.g. @prompt(‘Districts’,A,multi,free).
In a message dated 98-08-18 10:28:39 EDT, you write:
It seems as if the only values that is
considered is the first one (to the left of the first semi-colon). Does anyone know what we are doing wrong or if it is even possible to prompt for multiple values using free-hand SQL?
I have not tried it myself, but there is a common mistake that can be made when responding to prompts. This is usually the case when only the first value of a set is selected. Make sure you do not use embedded spaces when responding to the prompt - BusObj uses those spaces as part of the value.
Assume you are looking for the values ‘A’, ‘B’, and of course ‘C’.
When responding to a prompt, you should use this:
A;B;C
not the more user readable:
A; B; C
The reason is, the second response to a prompt generates the SQL:
IN (‘A’, ’ B’, ’ C’)
note the extra space in ’ B’ and ’ C’. ‘A’, however, is correct and will show up on the query.
If you leave the spaces out and respond with:
A;B;C
… then the SQL will be generated as:
IN (‘A’, ‘B’, ‘C’)
which will work for all three values. Hope this helps! Again, I do not know if this is the case with Free Hand SQL, but it is a definite issue with a standard universe query.
You Wrote:
When using a prompt (@Variable command) with an In List operator, you are
able to separate multiple values with semi-colons. However, when we create
a free-hand SQL query using the @Variable command, the semi-colon does not
seem to behave the same way. It seems as if the only values that is
considered is the first one (to the left of the first semi-colon). Does
anyone know what we are doing wrong or if it is even possible to prompt for
multiple values using free-hand SQL?
Thanks,
Peter Borjestedt g
Solution:
You are right. The way I found to get around this is to use the @Prompt() function instead of the @Variable(). Then, if you use semi-colons between the values, it works correctly.
