Error-Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. RSS

• • Reply
  jsshivalik

  Member

  140 Points

  518 Posts

  Error-Incorrect syntax near the keyword 'with'. If this statement is a common table expression, a...

  Jan 20, 2020 04:27 AM|jsshivalik|LINK

  Hi

    I am getting below 2 errors .

  Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

  Incorrect syntax near ','.

  Declare @frDate Date
  Declare @toDate Date
  Declare @locGSTN nvarchar(20)
  
  set @frDate = '2019/11/01'
  set @toDate = '2019/11/30'
  set @locGSTN = '03AANFP9725C1Z8'
  
  If(OBJECT_ID('tempdb..# Temp') Is Not Null)
  	begin
  		Drop Table #Temp
  	end
  	
  
  
  	select DocNum , DocDate , CardName, LocGSTN, (BaseSum) as BaseSum, 
  	       ItemTaxType,
  	       IsIntraState
  	into #Temp
  	from (select distinct DocEntry, LineNum, DocNum , DocDate , CardName, LocGSTN,
  				 case when ObjType = 19 then -BaseSum
  		         else BaseSum 
  		         end as BaseSum, ItemTaxType, IsIntraState
  		  from GST_ODOC_Result_Ignore_RvsCharge t0
  		  where ObjType in (18, 19)
  				and ItemTaxType = 'GN'
  				and PureNilRateDoc = 'Y'
  				and BpGSTType <> 3
  			    and DocDate >= @frDate
  			    and DocDate <= @toDate
  			    and LocGSTN = @locGSTN
  		  
  ) t0
  
  with
  	CTE1 as
  	(
  		select 'Nil rated supply' as "Description",
  		    convert(numeric(19, 2), isnull((case when IsIntraState <> 'Y' then BaseSum else 0 end), 0)) as "InterState",
  		    convert(numeric(19, 2), isnull((case when IsIntraState = 'Y' then BaseSum else 0 end), 0)) as "IntraState",
  		    1 as TabOrder
  		from #Temp
  		where ItemTaxType <> 'NN'
  
                  union all
  
  		select 'Exempt', null, null, null
                  where not exists
  			(select 1 from #Temp  where ItemTaxType <> 'NN')
  	),
  
  	CTE2 as
  	(
  		select 'Non GST supply' as "Description",
  		    convert(numeric(19, 2), isnull((case when IsIntraState <> 'Y' then BaseSum else 0 end), 0)) as "InterState",
  		    convert(numeric(19, 2), isnull((case when IsIntraState = 'Y' then BaseSum else 0 end), 0)) as "IntraState",
  		    1 as TabOrder
  		from #Temp
  		where ItemTaxType = 'NN'
  
                  union all
  
  		select 'Non GST supply', null, null, null
                  where not exists
  			(select 1 from #Temp where ItemTaxType = 'NN')
  	),
  
  	CTEU as
  	(
  		select * from CTE1
  
  		union
  
  		select * from CTE2
  	)
  
  select "Description" as "Nature of supplies",
  		   InterState as "Inter-State supplies",
  		   IntraState as "Intra-state supplies"
  from CTEU
  
  

  Thanks

• • Reply
  Khuram.Shahz...

  Contributor

  2096 Points

  1040 Posts

  Re: Error-Incorrect syntax near the keyword 'with'. If this statement is a common table expressio...

  Jan 20, 2020 05:28 AM|Khuram.Shahzad|LINK

  t0 should be to

  "Mark as Answer" if that helps you.
• • Reply
  limno

  All-Star

  123252 Points

  10024 Posts

  Moderator

  Re: Error-Incorrect syntax near the keyword 'with'. If this statement is a common table expressio...

  Jan 20, 2020 02:34 PM|limno|LINK

  ...the previous statement must be terminated with a semicolon.

  ;with

  Jingyang Li
  Format your SQL query with instant sql formatter:
  http://www.dpriver.com/pp/sqlformat.htm
• • Reply
  Yang Shen

  Contributor

  3140 Points

  983 Posts

  Re: Error-Incorrect syntax near the keyword 'with'. If this statement is a common table expressio...

  Jan 21, 2020 01:23 AM|Yang Shen|LINK

  Hi jsshivalik,

  jsshivalik

  Incorrect syntax near the keyword 'with'.

  As @limno mentioned, a CTE always needs a semicolon in front of the statement since the with can be used many ways in SQL Server, you can refer to Common Table Expression, why semicolon? for more information.

  jsshivalik

  Incorrect syntax near ','.

  As for this comma error, it's hard to tell since too many commas in the query, point out which one causes the error would be helpful.

  Best Regard,

  Yang Shen