troubleshooting Question

SQL Statement error

Avatar of lrollins
lrollinsFlag for United States of America asked on
DatabasesMicrosoft SQL ServerSQL
5 Comments1 Solution110 ViewsLast Modified:
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?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros