Let us assume
there is Student table in any database. Now we want to use common table
expression in SELECT, INSERT or UPDATE statement. For example:
SELECT * FROM Student
WITH cteCust
AS(
SELECT * FROM Student
)SELECT * FROM cteCust
We will get error
message like:
Incorrect syntax near ''. If this is intended to be a common
table expression, you need to explicitly terminate the previous statement with
a semi-colon.
Cause: When we
will use cte or common table expression previous statement must be terminated
by semi-colon.
Solution:
SELECT * FROM Student;
WITH cteCust
AS(
SELECT * FROM Student
)SELECT * FROM cteCust
Your example is very easy to understand but that is wrong in this case?
ReplyDeleteWITH ANCESTORS_PERSON_1 AS
(
select MOTHER_CNP, FATHER_CNP
from [dbo].[PERSON]
start with PERSON_ID=1
connect by PERSON_ID = prior MOTHER_CNP or PERSON_ID = prior FATHER_CNP
)
Incorrect syntax near 'PERSON_ID' but I do not understand why
I think your issue in this query:
ReplyDeleteselect MOTHER_CNP, FATHER_CNP
from [dbo].[PERSON]
start with PERSON_ID=1
connect by PERSON_ID = prior MOTHER_CNP or PERSON_ID = prior FATHER_CNP
I'm not sure what is start with,connect by and prior in this query. Are you able to execute this query with using common table expression?