Why does this sql not work as expected

[Former Member]

select * from vw_invoice where check_number in (
  select char(39)||list(args.row_value,char(39)||char(44)||char(39))||char(39)
       from sa_split_list('203134,202162',',')args)


If I run just select char(39)||list(args.row_value,char(39)||char(44)||char(39))||char(39)            from sa_split_list('203134,202162',',')args
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. 

[Former Member]

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.

I.e.

select * from vw_invoice where check_number in (
    select args.row_value
    from sa_split_list('203134,202162',',') args
)


Hope this helps!

[Former Member]

Actually that would not work, there are some values stored in this column that have characters in them, when the implict cast from number to char is applied by the database we get an error that says can't convert 'INV...' to an integer. This is why I am trying to basically convert a number list to a char list.

[Former Member]

Have you tried casting the value to a VARCHAR, or perhaps appending a dummy value to the split list that could never be in the result set or be considered an integer value?

select CAST(args.row_value AS VARCHAR(20)) -- or higher than 20 if needed
...
or
...
from sa_split_list('203134,202162,this_value_will_never_exist',',') args )

[Former Member]

I have tried the cast as varchar, but was still wrapping it in the char(39) single tics, I will give it a try as you have indicated. I have not tried appending the value that would not be there, will give it a shot also.

[Former Member]

Well have tried several different things all with the same results. Even thought the documentation states: LIST function [Aggregate] Returns a comma-delimited list of values.

It seems to treat the returned value as a single value not as a list of values.

Thanks Tyson.

[VolkerBarth]

LIST function [Aggregate] Returns a comma-delimited list of values.

It seems to treat the returned value as a single value not as a list of values.

That's no contradiction IMHO but expected behaviour: It's really the goal of LIST() to return one value consisting of a string concatenation of the according aggregated values, just as AVG() will return one value with the average of the according aggregated values.

In contrast, when using the IN predicate, you want to test your expression against a SET of values, such as is returned by an ordinary SELECT resultset or the sa_split_list() function.

---

What about casting the search expression to a VARCHAR, such as:

select * from vw_invoice
where cast(check_number as varchar(30)) in (
    select args.row_value
    from sa_split_list('203134,202162',',') args
)

[Former Member]

Hi Volker, I guess it is just my understanding of the documentation. When it says values (plural) to me it is saying a list comprised of individual values returned in a string. Like returning an array of comma delimited values. However if that is not how it works, I just need to understand it and use the function as intended. When I use the AVG function, visually you only see one value, I guess the trick to understanding this is thinking of values returned by the list function as a single textual value, no matter how you format the list string visually.

We have written code in the application to grab the values entered, properly format them into a string array and supply that as the argument to the datawindow.

I did try running your suggestion and received an error about subquery can only return one row.

Thanks to all of you for helping me to better understand this.

[Breck_Carter]

> When I use the AVG function, visually you only see one value

Yes, you do... AVG is an aggregate function, and so is LIST. The documentation is clear on this matter: "From each set of rows to which an aggregate function is applied, SQL Anywhere generates a single value."

Sadly, however, the documentation on LIST() does NOT absolutely-positively-without-a-shadow-of-a-doubt state that it returns a string... and it should.

FWIW the LIST() function can be combined with other functions like STRING() to construct massively complex clobs, otherwise known as web pages, in their entirety, using a single SELECT or combination thereof... in other words, LIST() rocks! 🙂

[Former Member]

Okay, let's try this one (actually it is only a variation of Volkers suggestion):

select * 
  from vw_invoice
 where check_number in (select cast(row_value as varchar) 
                          from sa_split_list('203134,202162,47typo11', ','))

Should avoid the "subquery single return" and the "can't convert" error as well.

Btw because you talk about a datawindow I guess you are using PowerBuilder. I believe there are better solutions to this task.

Good luck,

Chris Werner