For XML Path used to Concatenate with a comma separated string - Error message first time run - then it seems to work
-
The first time this code is run, there is an error message (See attachment).
After clicking OK, it just runs fine.
Is this error message normal? See linked image:
There exist a table that takes a PK in one column, then Concats the (STIP abbreviation, start Day/MO, End Day/MO)
Sometimes, the PK had several STIP records.
The code below combines all of the STIPs in the same PK and Concats them with a comma to seperate them. (See Attachment)
This view is being consumed by another view.
The view that consumes this doesn't bring up this error message.
SELECT STIP2.[ID_Wells], substring
((SELECT ', ' + STIP1.[STIPwDate] AS [text()]
FROM [RegulatoryDB].[dbo].[vRegulatory_Nav_GIS_Stips] STIP1
WHERE STIP1.[ID_Wells] = STIP2.[ID_Wells]
ORDER BY STIP1.[ID_Wells] FOR XML PATH('')), 2, 1000) [STIPwDate]
FROM [RegulatoryDB].[dbo].[vRegulatory_Nav_GIS_Stips] STIP2
Some of the STIPS (e.g. PM) Don't have a date, so we see PM()
Once in a while, there is this extra close parens. No rhyme or reason.
-
Mile Higher Than Sea Level (6/10/2015)
The first time this code is run, there is an error message (See attachment).After clicking OK, it just runs fine.
Is this error message normal? See linked image:
There exist a table that takes a PK in one column, then Concats the (STIP abbreviation, start Day/MO, End Day/MO)
Sometimes, the PK had several STIP records.
The code below combines all of the STIPs in the same PK and Concats them with a comma to seperate them. (See Attachment)
This view is being consumed by another view.
The view that consumes this doesn't bring up this error message.
SELECT STIP2.[ID_Wells], substring
((SELECT ', ' + STIP1.[STIPwDate] AS [text()]
FROM [RegulatoryDB].[dbo].[vRegulatory_Nav_GIS_Stips] STIP1
WHERE STIP1.[ID_Wells] = STIP2.[ID_Wells]
ORDER BY STIP1.[ID_Wells] FOR XML PATH('')), 2, 1000) [STIPwDate]
FROM [RegulatoryDB].[dbo].[vRegulatory_Nav_GIS_Stips] STIP2
Some of the STIPS (e.g. PM) Don't have a date, so we see PM()
Once in a while, there is this extra close parens. No rhyme or reason.
Your query looks fine to me. Perhaps it's the Query builder GUI...
Try copying and pasting that query into an SSMS query window and executing it from there...
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
But of course!
I am still a Newbie using training wheels.
That was it, thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply