Remove missing semi-colon Error from WITH statement?

Question

I'm trying to run the following query:

With T As (Select A.TerminationDate,
        B.TerminationApproach,
        B.Date,
        A.UserName,
        A.LastName,
        A.FirstName,
        Case
          When B.Date <= A.TerminationDate Then 0
          Else 1
        End As EvalCheck
      From A 
      Left Join B On B.ChangedUser = A.UserName)
Select T.UserName,
  T.seqnum,
  T.TerminationDate,
  T.TerminationApproach,
  T.Date,
  T.LastName,
  T.FirstName,
  T.EvalCheck
From (Select T.*,
        Row_Number() Over (Partition By T.UserName Order By T.Date) As
        seqnum
      From T) T;
Where T.seqnum = 1

And get the following error on my SQL database when trying to execute it:

Maybe there is a better way to do this query and get rid of the error?

Is there other code in the batch before the `WITH` statement? If so just prepend a semicolon, i.e.: `; WITH T As....` — AlwaysLearning, Mar 01 '20 at 21:49
Does this answer your question? [Common Table Expression, why semicolon?](https://stackoverflow.com/questions/6938060/common-table-expression-why-semicolon) — Dale K, Mar 01 '20 at 21:51
Although the duplicate answer is a bit out of date, now the recommendation is to terminate all statements with a semicolon rather than just pre-pending with and merge. — Dale K, Mar 01 '20 at 21:53
...Also, `From T) T; Where T.seqnum = 1` has a misplaced semicolon. Remove that. — Zohar Peled, Mar 01 '20 at 21:59
There is no other statement before the WITH statements. The semicolon after the ```T;```was a mistake, but does not solve the problem. Also I have already tried to solve it the way it was described in the other question, but that didn't work either - so the issue remains. — goatw, Mar 01 '20 at 22:23
I can't reproduce your issue, once you remove the incorrect `;` from your query it works for me. — Dale K, Mar 01 '20 at 22:35
I am working on a web-based tool, which has a query builder build inside - this might also cause some problems and that's why I wanted to try a different approach which @Gordon Linoff provided and worked so far! — goatw, Mar 01 '20 at 22:49
@goatw, it seems the tool you are using adds another statement before the CTE that isn't semi-colon terminated (e.g. `SET NOCOUNT ON`). — Dan Guzman, Mar 02 '20 at 01:37

Gordon Linoff · Accepted Answer · 2020-03-02T02:19:52.640

1

Why use a CTE at all?

Select A.TerminationDate, B.TerminationApproach, B.Date,
       A.UserName, A.LastName, A.FirstName,
       (Case When B.Date <= A.TerminationDate Then 0
             Else 1
        End) As EvalCheck
From A Left Join
     (select b.*,
             row_number() over (partition by B.ChangedUser order by B.date asc) as seqnum
      from B
     ) B
     On B.ChangedUser = A.UserName and B.seqnum = 1;

edited Mar 02 '20 at 02:19

answered Mar 01 '20 at 22:07

Gordon Linoff

1,242,037
58
646
786

If I change ```...order by B.date asc) as...``` it works. – goatw Mar 01 '20 at 22:33
@goatw . . . Yes. You seem to want the oldest date rather than the newest date. – Gordon Linoff Mar 02 '20 at 02:20

score -1 · Answer 2 · answered Mar 01 '20 at 22:30

-1

Because the keyword WITH is overloaded, the position of it in the statement is important. In a CTE it must be first, and this is guaranteed by immediately preceding it with a semicolon.

answered Mar 01 '20 at 22:30

Matt Bowler

181
8

Its now the official recommended practice to correctly terminate all statements with a semicolon, not to prepend to `with` and `merge`. – Dale K Mar 01 '20 at 22:36

Remove missing semi-colon Error from WITH statement?

2 Answers2