Error - Conversion failed when converting the varchar value 'Grand Total' to data type int.[Answered] RSS

• • Reply
  jsshivalik

  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

  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)

  Thanks

• • Reply
  Sean Fang

  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

  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 >