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)
Test0 Data:
Test1 Data:
Result:
Hope this can help you.
Best regards,
Sean
ASP.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >
Member
140 Points
515 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
Contributor
3020 Points
889 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