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?
Open in new window