If I run just
I get the nicely formed argument '203134','202162' the first query will not return any rows using this method to convert the comma separated listing of numbers to a comma separated varchar listing.
I wanted my users to be able to type in the number list separated by commas, and then convert it to strings, as that is the datatype of the column where it is stored.
I also tried putting it between begin and end blocks, and assigning the rebuilt string to a variable, and then passing that variable to the query. I get the correct '203134','202162' assigned to the variable, but it returns no rows. However if I replace the variable with '203134','202162' it returns data.
When you are using the LIST function to concatenate the different values, you are essentially making them one value. If this string you then built was strictly sent as part of the SQL, the values would be parsed individually as a list. Because they are concatenated as one result from a select, however, they are considered a single value (call this value var_x, where var_x = '''203134'',''202162'''). Neither of the values in the original list will work in the select then because '203134' <> var_x and '202162' <> var_x.
I suspect you'll get the desired behavior by removing the LIST aggregate function.
Hope this helps!
answered 25 Apr '14, 20:48