Mar 21, 2012

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.


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

2 comments:

  1. Anonymous10/22/2013

    Your example is very easy to understand but that is wrong in this case?
    WITH 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

    ReplyDelete
  2. I think your issue in this query:
    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

    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?

    ReplyDelete