Subselect in sa_split_list

[Former Member]

When I try:

select line_num, row_value as cc_test FROM sa_split_list( (select top 1 start at 3 substr(cc_txt,12) FROM tblCategory), ';' ) where line_num = 1;

I get syntax error near ','. Why

[VolkerBarth · Accepted solution]

Here's a sample with a (rather useless) subquery on the system table SYSTAB, returning a date value.

-- It "packs" the expected value as column "x" of a derived query (DT)
-- and then calls the stored procedure for that according column, split by a hyphen.
--
-- Note: If you do not apply the "TOP 1" clause, DT will return several rows
-- and the procedure will be called for each result set row separately
-- (in case that may be helpful here)

select line_num, row_value
from (select top 1 last_modified_at as x from systab order by 1) DT
   CROSS APPLY sa_split_list(DT.x, '-')
-- where line_num = 1