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

  • Question

  • User1979860870 posted

    Hi

      Conversion failed when converting the varchar value 'Sub Total :-' to data type int.

    (Case when T1.LNum IS Null then 'Sub Total :-' Else LNum End) AS "No.",

    Thanks

    Friday, January 1, 2021 7:21 AM

Answers

  • User452040443 posted

    Hi,

    The data type of the values returned by the Case must be compatible.

    An alternative for your case would be to convert the LNum column to varchar:

    Case when T1.LNum IS Null then 'Sub Total :-' Else cast(LNum as varchar(20)) End AS "No.",

    or 

    coalesce(cast(T1.LNum as varchar(20)), 'Sub Total :-') AS "No.",

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 1, 2021 6:53 PM

All replies

  • User452040443 posted

    Hi,

    The data type of the values returned by the Case must be compatible.

    An alternative for your case would be to convert the LNum column to varchar:

    Case when T1.LNum IS Null then 'Sub Total :-' Else cast(LNum as varchar(20)) End AS "No.",

    or 

    coalesce(cast(T1.LNum as varchar(20)), 'Sub Total :-') AS "No.",

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 1, 2021 6:53 PM
  • User-1716253493 posted

    When you use CASE, in both side inside THEN and ELSE part should have same datatype

    both are varchar or int

    then 0 Else LNum End
    then null Else LNum End

    Above values for int datatype, if you want varchar then convert Lnum as varchar first. So both side are varchar.

    Friday, January 1, 2021 7:57 PM