SQL Statement error

I have the following sql server script and want to create a view with it.

WITH ROLLUP(ID,PARENT_ACCT_ID,[Level],Description, Active_flag, Type, Currency_ID) AS (SELECT ID, PARENT_ACCT_ID, 0 as Level, Description, Active_Flag, Type, Currency_ID
FROM ACCOUNT where PARENT_ACCT_ID is null
UNION ALL
SELECT R1.ID, R1.PARENT_ACCT_ID, R2.Level + 1, R1.Description, R1.Active_Flag, R1.Type, R1.Currency_ID
FROM ACCOUNT R1
JOIN ROLLUP R2
ON R1.PARENT_ACCT_ID = R2.ID)
SELECT ID,
CASE WHEN [Level] = 0 THEN ID ELSE '' END AS L0,
CASE WHEN [Level] = 1 THEN ID ELSE '' END AS L1,
CASE WHEN [Level] = 2 THEN ID ELSE '' END AS L2,
CASE WHEN [Level] = 3 THEN ID ELSE '' END AS L3,
CASE WHEN [Level] = 4 THEN ID ELSE '' END AS L4,
CASE WHEN [Level] = 5 THEN ID ELSE '' END AS L5,
CASE WHEN PARENT_ACCT_ID IS NOT NULL THEN PARENT_ACCT_ID ELSE '' END AS PARENT_ACCT,
Description, Active_Flag, Type, Currency_ID
FROM ROLLUP
GROUP BY [Level], ID, PARENT_ACCT_ID, Description, Active_Flag, Type, Currency_ID

The field called Level isn't a field in the database table. When I try to execute the script, I get the following error:

Error in SELECT clause: expression new 'LEVEL'.
Missing FROM clause.
Unable to parse query text.

Can anyone tell me where to look for the error?

DatabasesMicrosoft SQL ServerSQL

Last Comment

PortletPaul

8/22/2022 - Mon

ste5an

11/19/2019

The basic problem is your that you're using a lot of reserved words (ROLLUP in the concrete case as part of the GROUP BY clause). And I would use the common aliases in the CTE (A for anchor, P for parent and C for child). E.g.

WITH Hierarchy ( ID, PARENT_ACCT_ID, [Level], Description, Active_flag, [Type], Currency_ID ) AS
(
    SELECT  A.ID,
            A.PARENT_ACCT_ID,
            0,
            A.Description,
            A.Active_Flag,
            A.[Type],
            A.Currency_ID
    FROM    ACCOUNT A
    WHERE   A.PARENT_ACCT_ID IS NULL
    UNION ALL
    SELECT  C.ID,
            C.PARENT_ACCT_ID,
            P.Level + 1,
            C.Description,
            C.Active_Flag,
            C.[Type],
            C.Currency_ID
    FROM    ACCOUNT C
        INNER JOIN Hierarchy P ON C.PARENT_ACCT_ID = P.ID
)
SELECT  ID,
        CASE WHEN [Level] = 0 THEN ID ELSE '' END AS L0,
        CASE WHEN [Level] = 1 THEN ID ELSE '' END AS L1,
        CASE WHEN [Level] = 2 THEN ID ELSE '' END AS L2,
        CASE WHEN [Level] = 3 THEN ID ELSE '' END AS L3,
        CASE WHEN [Level] = 4 THEN ID ELSE '' END AS L4,
        CASE WHEN [Level] = 5 THEN ID ELSE '' END AS L5,
        CASE WHEN PARENT_ACCT_ID IS NOT NULL THEN PARENT_ACCT_ID ELSE '' END AS PARENT_ACCT,
        Description,
        Active_Flag,
        [Type],
        Currency_ID
FROM    Hierarchy
GROUP BY [Level],
        ID,
        PARENT_ACCT_ID,
        Description,
        Active_Flag,
        [Type],
        Currency_ID;