SQL Statement error
I have the following sql server script and want to create a view with it.
WITH ROLLUP(ID,PARENT_ACCT_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?
WITH ROLLUP(ID,PARENT_ACCT_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?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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;
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...
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...
Premium Content
You need an Expert Office subscription to comment.Start Free Trial