Hi all,

is it possible to use sa_split_list over a table column, i.e. something like
select * from sa_split_list((select myListColumn from mytable))
?
If so, is it only valid when the select will jst return 0 or 1 rows?


Note, the call above is syntactically wrong.

TIA

Volker

While I would still like to get an answer, I worked around this problem
with the obvious solution of using a for-statement like:

for forList as crsList cursor for
select col1, myListColum from mytable
for read only
do
-- use each row_value for any action, e.g. put it in a temp table LT
insert LT (col1, myUnlistedColum)
select col1, cast(row_value as int)
from sa_split_list(myListColumn)
order by line_num;
end for;

Volker


Volker Barth wrote:

You can use the LATERAL derived-table syntax to use a join with outer
references to tables earlier in the FROM clause. A contrived example tested
with 10.0.1(3932). If you are using more recent versions, the OPENSTRING
syntax may be of interest as it offers some additional flexibility such as
decoding multiple columns and specifying the data type. This functionality
can also be accomplished with XML support. These mechanisms enable some
interesting solutions that pack rows into a scalar and then decode them.

select "precision" x, list(domain_name) str
into #tab
from sysdomain
group by x;

select x, line_num, row_value
from #tab, lateral( sa_split_list(str) ) P

Regards,
--
Ivan T. Bowman
SQL Anywhere Research and Development
[Sybase iAnywhere]

"Volker Barth" <No_VBarth (AT) Spam_GLOBAL-FINANZ (DOT) de> wrote

Ivan,

thanks for the explanation!

"Lateral procedure calls" are still quite unfamiliar to me, though
interestingly enough I have used them in the same stored procedure that
raised the sa_split_list() question.

Sometimes it's just hard to find out how it's all working together, even
if one knows most of the parts...

Generally, I' favour the lateral call solution (even over OPENSTRING) as
it seems to be a clearer counterpart to the LIST aggregate.

But in the current case I have decided to keep the cursor based
approach. The lateral call would lead to an "insert on existing update
select" over a lateral call joined to a doubly derived table. - That
might work well for the optimizer but not for my brain

Thanks again!
Volker




Ivan T. Bowman [Sybase iAnywhere] wrote: