Link to home
Start Free TrialLog in
Avatar of lrollins
lrollinsFlag 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?
Avatar of ste5an
ste5an
Flag of Germany image

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
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Point bring made is you have to avoid ambiguity by explicitly defining the table from which column level is used in the comparison evaluation
Avatar of lrollins

ASKER

PorletPaul,   Thanks.  That was the exact problem.
Glad I could help. Cheers. Paul