in Below code i am getting above error. DocNum is int type
SELECT Coalesce(T1.[DocNum],'Grand Total') as DocNum
, sum(T0.Quantity) FROM Test0 T0 INNER JOIN Test1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T1.[DocDate] >= '2020/04/01' and T1.[DocDate] <= '2020/04/15'
group by RollUp(T1.DocNum)
You might get error message about the function "Coalesce()" that conversion failed when converting the varchar value 'Grand Total' to data type int.
What you need to do is to add cast function to make DocNum a
varchar type so that the "Grand Total" could be converted.
More details, you could refer to below SQL statement and simulation of the data:
SELECT Coalesce(CAST(T1.[DocNum] as varchar),'Grand Total') as DocNum
, sum(T0.Quantity) FROM Test0 T0 INNER JOIN Test1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T1.[DocDate] >= '2020/04/01' and T1.[DocDate] <= '2020/04/15'
group by RollUp(T1.DocNum)
Member
130 Points
482 Posts
Error - Conversion failed when converting the varchar value 'Grand Total' to data type int.
Apr 19, 2020 07:11 AM|jsshivalik|LINK
Hi
in Below code i am getting above error. DocNum is int type
Thanks
Participant
1110 Points
299 Posts
Re: Error - Conversion failed when converting the varchar value 'Grand Total' to data type int.
Apr 19, 2020 08:05 AM|Sean Fang|LINK
Hi jsshivalik,
You might get error message about the function "Coalesce()" that conversion failed when converting the varchar value 'Grand Total' to data type int.
What you need to do is to add cast function to make DocNum a varchar type so that the "Grand Total" could be converted.
More details, you could refer to below SQL statement and simulation of the data:
SELECT Coalesce(CAST(T1.[DocNum] as varchar),'Grand Total') as DocNum , sum(T0.Quantity) FROM Test0 T0 INNER JOIN Test1 T1 ON T0.DocEntry = T1.DocEntry WHERE T1.[DocDate] >= '2020/04/01' and T1.[DocDate] <= '2020/04/15' group by RollUp(T1.DocNum)Test0 Data:
Test1 Data:
Result:
Hope this can help you.
Best regards,
Sean