Avatar of lrollins
lrollins
Flag for United States of America asked on

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

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
ste5an

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;

Open in new window

ASKER CERTIFIED SOLUTION
PortletPaul

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
arnold

Point bring made is you have to avoid ambiguity by explicitly defining the table from which column level is used in the comparison evaluation
lrollins

ASKER
PorletPaul,   Thanks.  That was the exact problem.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
PortletPaul

Glad I could help. Cheers. Paul