locked
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. RRS feed

  • 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

    Monday, January 20, 2020 4:27 AM

All replies

  • User-1780421697 posted
    t0 should be to
    Monday, January 20, 2020 5:28 AM
  • User77042963 posted

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

    ;with

    Monday, January 20, 2020 2:34 PM
  • 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

    Tuesday, January 21, 2020 1:23 AM