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

asked 29 Sep '17, 05:09

Rolle's gravatar image

Rolle
558495161
accept rate: 0%


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
permanent link

answered 29 Sep '17, 07:29

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 30 Sep '17, 07:26

Are you using version 12 or below?

In those versions a procedure does not accept subqueries as argument, such as your "(select top...)".

With v16, that was changed, and your query should work here.

In v12, you could use a derived query to get the top result and then use CROSS APPLY to provide the result as argument to the procedure. The forum does contain such samples, search for APPLY or LATERAL...

permanent link

answered 29 Sep '17, 06:58

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 29 Sep '17, 07:02

I am using SA12. Can you show me how I use Cross Apply in this case? I have searched but do not understand how to do. I found an openstring solution, but it had its limitations as well.

(29 Sep '17, 07:16) Rolle
Your answer
toggle preview

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×69
×22

question asked: 29 Sep '17, 05:09

question was seen: 2,548 times

last updated: 30 Sep '17, 07:26