none
Conversion failed when converting the varchar value to data type int. RRS feed

  • Question

  • Hello all, I'm getting the error as stated in the title. The error only began to happen when I added the LEFT JOIN. Any tips on possible solutions? 
    Select 
    fdate,
    fsdatetime, 
    fedatetime,
    fshift,
    fjobno,
    SumForThisDate ,
    SumForAllDates,
    SumForAllDates/V_COUNT as PerDay
    From
    (
    Select
    fdate,
    fsdatetime, 
    fedatetime,
    fshift,
    fjobno,
    SumForAllDates,
    SumForThisDate,
    ROW_NUMBER() OVER (ORDER BY fdate) AS RN,
    COUNT(1) OVER () AS V_COUNT
    From
    (Select  fdate,fsdatetime, fedatetime,fshift, ld.fjobno,fcompqty ,
    SUM(fcompqty) Over(PARTITION BY fdate) As SumForThisDate,
    SUM(fcompqty) Over() As SumForAllDates,
    ROW_NUMBER() OVER (PARTITION BY fdate ORDER BY fdate,fsdatetime, fedatetime,fshift, ld.fjobno,fcompqty ) AS RowNumber
    from ladetail ld
    left join jodrtg on ld.fjobno+ld.foperno=jodrtg.fjobno+jodrtg.foperno
    where ld.fjobno='IT713-0000' and fcompqty<>0) V
    Where V.RowNumber = 1
    ) a
    WHERE RN=V_COUNT

    Wednesday, January 2, 2019 3:58 PM

Answers

  • And you sum the values of both columns and this cause the error; you have to cast explicit the integer to varchar, like

    left join jodrtg on ld.fjobno+CONVERT(varchar(9), ld.foperno)=jodrtg.fjobno+CONVERT(varchar(9), jodrtg.foperno)


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by David9501 Wednesday, January 2, 2019 4:25 PM
    Wednesday, January 2, 2019 4:23 PM

All replies

  • Then you have somewhere an implicit type conversion, e.g. from varchar to integer and that fails. So check all data types in your new JOIN clause.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, January 2, 2019 4:04 PM
  • For fjobno the data type is char and foperno is int.
    Wednesday, January 2, 2019 4:16 PM
  • And you sum the values of both columns and this cause the error; you have to cast explicit the integer to varchar, like

    left join jodrtg on ld.fjobno+CONVERT(varchar(9), ld.foperno)=jodrtg.fjobno+CONVERT(varchar(9), jodrtg.foperno)


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by David9501 Wednesday, January 2, 2019 4:25 PM
    Wednesday, January 2, 2019 4:23 PM