I have a table Source
that contains a column tags
that has a string data type.
Here is an example of a row from the column:
["tag_a", "tag_b", "tag_c", "tag_d"]
The following code extracts each tag and creates a new column through a cross apply with the original tag list.
SELECT DISTINCT tags as TAGS_LIST, TRIM(' "[]' from value) as TAG FROM DB.dbo.Source CROSS APPLY STRING_SPLIT(tags, ',');
The previous example would yield the following table:
TAGS_LIST
TAG
["tag_a", "tag_b", "tag_c", "tag_d"]
tag_a
["tag_a", "tag_b", "tag_c", "tag_d"]
tag_b
["tag_a", "tag_b", "tag_c", "tag_d"]
tag_c
["tag_a", "tag_b", "tag_c", "tag_d"]
tag_d
It works fine on its own.
However, I am unable to fit it into a Multi-statement Table-valued Function:
CREATE FUNCTION [dbo].[fnTAGS](@STARTDATE DATETIME, @ENDDATE DATETIME) RETURNS TABLE AS RETURN ( SELECT DISTINCT tags as TAGS_LIST, TRIM(' "[]' from value) as TAG FROM DB.dbo.Source WHERE DB.dbo.Source.[creation_date] >= @STARTDATE AND DB.dbo.Source.[creation_date] <= @ENDDATE CROSS APPLY STRING_SPLIT(tags, ',') );
This gives me the following error: Incorrect syntax: 'CREATE FUNCTION' must be the only statement in the batch.
Placing it before WHERE
also doesn’t work.
I could not find any specification telling me CROSS APPLY
could not be placed inside a Multi-statement Table-valued Function.
How can I have the CROSS APPLY
inside of a Multi-statement Table-valued Function?
I have tried positioning the cross apply in different places of the functions.
I have tried researching on CROSS APPLY
inside of a Multi-statement Table-valued Function, but have only found articles that talk about them as separate.
You need to sign in to view this answers