Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2009
    Posts
    20

    Unanswered: CTE in sql server 2008

    Hello every body,

    I have been facing this problem since long. I have a table student and I am defining a CTE on that using SQl server 2008 with following code:

    with cte_student(cte_ID,cte_name,cte_fees)
    AS
    (select ID,name,fees from student where fees>5000 group by ID)

    and it gives me followig error "Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near ')'."


    . I dont see any apparant reason behind it . may be some body can help.

    Kunaal

  2. #2
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    You can't just end a CTE definition with that, you need to do some processing on the result.

    The error is telling you that it is expecting more code after your closing bracket...

    I.e.
    Code:
    with cte_student(cte_ID,cte_name,cte_fees)
    AS
    (select ID,name,fees from student where fees>5000 group by ID,name,fees)
    select * from cte_student

  3. #3
    Join Date
    Jun 2009
    Posts
    20
    Thanks EngadaSQL,

    That really worked for me

    Kunal

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Which really begs the question - why are you using a CTE in this scenario?
    Code:
    SELECT id As cte_id
         , name As cte_name
         , fees As cre_fees
    FROM   student As cte
    WHERE  fees > 5000
    furthermore, your original query does have a syntaxt error - any columns not included in your GROUP BY definition must be aggregated (e.g. Sum, Max, Count).
    George
    Home | Blog

  5. #5
    Join Date
    Jun 2009
    Posts
    20
    Hi george,

    Actually I am new to SQL server and I was learning CTE. I wrote a sample code which did not run properly so I posted the question on the forum. But yes I really got my answers thank you very much guys.

    Kunal

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •