CODEWITHSUNDEEP
sqlsql-server
Kartikeya Sharma
Kartikeya Sharma

Reputation: 1383

Incorrect syntax near ')' Error in the CTE

I wrote the following CTE (Common Table Expression)

WITH PRODUCTION_CTE(ShortProdNo,BoatRefNumber,ProdNo, CustomerPoNumber,LoadDate, Trailer, VeadaBuilding)
AS 
(
    SELECT 
    FBS.BoatNumber AS ShortProdNo,
    UOD.BoatRefNumber AS BoatRefNumber,
    FBS.ProdNo AS ProdNo,
    UOD.CustomerPoNumber AS CustomerPoNumber,
    FBS.Shipped AS LoadDate, 
    FBS.TruckNum AS Trailer,
    (CASE 
    WHEN Rtrim(UOD.CustomerPoNumber)='VEADA-VS1' THEN  'Bldg10'
     ELSE 'Bldg4'
     END) AS VeadaBuilding
FROM SysproCompanyV.dbo.FlatBenningtonShipping as FBS
INNER JOIN SysproCompanyV.dbo.UsrOrderDetails as UOD
ON FBS.BoatNumber=UOD.BoatRefNumber)

I am getting the following Error from the above CTE:

Msg 102, Level 15, State 1, Line 17
Incorrect syntax near ')'.

I am not sure why it is happening as Inner Joins are allowed in the CTE, all the parenthesis are closed and the names are correctly declared.

Upvotes: 1

Views: 58

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You need to call the CTE (i.e. PRODUCTION_CTE) IMMEDIATE after declaration : 

;with PRODUCTION_CTE as (
      . . . 
)

select pc.*
from PRODUCTION_CTE pc

Upvotes: 5

Related Questions