RE: Concat SQL statement, Johan, 02-23-2006
Couple of corrections
by Roger Harris, February 23, 2006 13:02
1. There isn't any concatenation operator "&" used in ODBscript; you just place the variables or literal strings to be concatenated one after the other.
2. You need to be careful with quotes that need to be embedded in the result string, since literal strings may or may not be quoted in SET values, but if they are quoted then the quotes will be removed. String literals don't need to be quoted unless they contain any special characters that might be interpreted as an operator (e.g. arithmetic), or commas (since commas are used to separate multiple SET variable=value pairs), or quotes (single or double) that you want included in the result string, or any $variable$ references that you DON'T want to be replaced with a value at that point. Like HTML tag parameters, you can quote SET values with single or double quotes, and if you need either one of those in the result string, then use the other to quote the whole string. (For example, since you need the single quotes for the region='value' in the SQL statement, you can use double quotes around the SET value that you're concatenating, and the embedded single quotes will be included as part of the string.) In this case, you also need to be careful that the $name$ variable that you are using to insert the value extracted from the input string is NOT in quotes, because you want the variable to be "de-referenced" to its value immediately at that point. (Otherwise, only "$name$" will go into the result string, and when you finish the loop and use that result string variable, the CURRENT value of name -- i.e., only the last one extracted in the loop -- will be inserted for ALL the $name$ references in the string. If $name$ is NOT in quotes, however, then the current value inside the loop will be inserted into the SET value.)
3. You don't want to end up with an extra "or region =" at the end of the loop, and I think the easiest way to do that in this case is to always put "region=" in front of each value as it's added to the string, and to insert the "or" only if there is already at least one value in the string. (And the easiest way to test that is to use the $if(...) "immediate IF" function to test whether or not the string is currently empty, and insert "or" if it's not empty.)
4. Remember that ODBscript is basically a "string language" so you need to identify variables used in expressions as $variable$ or the variable's name will be taken as plain text.
So, something like this should work:<% SET input="Europe,Asia Pacific,Greater China";
SET whereClause = "";
WHILE $input$; SET name = $split($input$, ","); SET whereClause = $whereClause$ $if($whereClause$,"or ") "region='"$name$"'"; ENDWHILE;
SELECT * from table where $whereClause$; %>
Name E-mail optional TopicMessage:
HTTP Link: Link text: