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
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
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.
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 ','.
Thanks
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
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
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
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,
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.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