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.

Question

User-797751191 posted

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

Answer 1

User-1780421697 posted

t0 should be to

Answer 2

User77042963 posted

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

;with

Answer 3

User-719153870 posted

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