Hi! I'm wondering if there is a function to split a comma separated string to array for use in the data list SQL query
I can see that this might be a confusing question, but I have to give it a try.
I'm joining two tables and am using data from the url string like this &news=1,2,3&items=69,34,65, representing @Request("news") and @Request("items").
This should be used in an SQL as the example under, but MSSQL needs to convert it to an array of INTs rather than a string if I want to use it in an IN statement.
Does DW have any functions in the SQL server that I can use to insert the @Request("news") and @Request("items") in an "WHERE id IN ()" statement?
SELECT
i.Id,
i.Dato,
i.Tittel,
i.Bilde,
i.Kategori,
'item' Type
FROM ItemType_Nyhet i
OUTER APPLY
(
SELECT TOP 1 *
FROM Paragraph P
WHERE i.Id = P.ParagraphItemId
) p
WHERE p.ParagraphShowParagraph = 'true' AND
GETDATE() > i.dato AND i.dato IS NOT NULL AND
i.Id IN (@Request("items"))
UNION
SELECT
n.NewsID Id,
n.NewsCreatedDate Dato,
n.NewsHeading Tittel,
n.NewsSmallImage Bilde,
convert(nvarchar(max),r.CustomFieldRowValue) Kategori,
'news' Type
FROM News n
OUTER APPLY
(
SELECT TOP 1 *
FROM AccessCustomFieldRow R
WHERE n.NewsID = R.CustomFieldRowItemID AND R.CustomFieldRowFieldID = 21
) r
WHERE n.NewsActive = 'true' AND
GETDATE() >= n.NewsActiveFrom AND
GETDATE() <= n.NewsActiveTo AND
n.NewsID IN (@Request("news"))
ORDER BY Dato DESC