Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in August and this forum will be retired.

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.

asked 25 Apr '14, 20:31

trexco's gravatar image

accept rate: 0%

edited 25 Apr '14, 22:44

Graeme%20Perrow's gravatar image

Graeme Perrow

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.


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

Hope this helps!

permanent link

answered 25 Apr '14, 20:48

Tyson%20Lewis's gravatar image

Tyson Lewis
accept rate: 22%

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.

(25 Apr '14, 20:56) trexco
Replies hidden

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
from sa_split_list('203134,202162,this_value_will_never_exist',',') args )
(25 Apr '14, 21:07) Tyson Lewis

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.

(26 Apr '14, 20:19) trexco

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.

(26 Apr '14, 21:12) trexco
Replies hidden

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

(27 Apr '14, 09:06) Volker Barth
Comment Text Removed

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.

(28 Apr '14, 12:42) trexco
Replies hidden

> 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! :)

(28 Apr '14, 14:25) Breck Carter

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

(28 Apr '14, 15:06) Chris Werner
showing 5 of 8 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 25 Apr '14, 20:31

question was seen: 2,474 times

last updated: 28 Apr '14, 15:39