SQL Statement error

lrollins
lrollins used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
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

EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
The error message states: "Error in SELECT clause: expression new 'LEVEL'."

I would carefully inspect the query at every use of the word "level" and ensure you use brackets for every instance of that word e.g.

as is:
SELECT R1.ID, R1.PARENT_ACCT_ID, R2.Level + 1, R1.Descrip...

to be:
SELECT R1.ID, R1.PARENT_ACCT_ID, R2.[Level] + 1, R1.Descrip...
Distinguished Expert 2017

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

Author

Commented:
PorletPaul,   Thanks.  That was the exact problem.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Glad I could help. Cheers. Paul

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial