T-sql Common expression query as subquery
Posted
by ase69s
on Stack Overflow
See other posts from Stack Overflow
or by ase69s
Published on 2010-03-31T09:06:58Z
Indexed on
2010/03/31
9:13 UTC
Read the original article
Hit count: 640
I have the following query:
WITH Orders(Id)
AS (
SELECT DISTINCT anfrageid FROM MPHotlineAnfrageAnhang
)
SELECT Id,
(
SELECT CONVERT(VARCHAR(255),anfragetext) + ' | '
FROM MPHotlineAnfrageAnhang
WHERE anfrageid = Id
ORDER BY anfrageid, erstelltam
FOR XML PATH('')
) AS Descriptions
FROM Orders
Its concatenates varchar values of diferents rows grouped by an id. But now i want to include it as a subquery and it gives some errors i cant solve. Simplified example of use:
select descriptions from
(
WITH Orders(Id)
AS (
SELECT DISTINCT anfrageid FROM MPHotlineAnfrageAnhang
)
SELECT Id,
(
SELECT CONVERT(VARCHAR(255),anfragetext) + ' | '
FROM MPHotlineAnfrageAnhang
WHERE anfrageid = Id
ORDER BY anfrageid, erstelltam
FOR XML PATH('')
) AS Descriptions
FROM Orders
) as tx where id=100012
Errors (Aproximate translation from spanish):
-Incorrect sintaxis near 'WITH'.
-Incorrect sintaxis near 'WITH'. If the instruction is a common table expression or a xmlnamespaces clause, the previous instruction must end with semicolon.
-Incorrect sintaxis near ')'.
What im doing wrong?
© Stack Overflow or respective owner