locked
Error - Conversion failed when converting the varchar value 'Grand Total' to data type int. RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    User-797751191 posted

    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

    Sunday, April 19, 2020 7:11 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    User-1330468790 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, April 19, 2020 8:05 AM