SQL Server Error Correction – Conversion Failed for nvarchar to int

sql server

I am running into this error whenever I run my query.
enter image description here

I believe to fix this issue I have to Format the column which holds those values into a number by itself. I have already tried the Format() and Replace() functions, but they did not work for me. Any other suggestions?

SELECT
   e.accountingcode AS [Vehicle],
   Concat(e.year + ' ', e.make + ' ', e.model + ' ', Cast( e.[Description] AS VARCHAR(80))) AS NAME,
   Iif(cff.stringtype = '', cff1.stringtype, cff.stringtype) AS 'Operator',
   fta.fluid,
   so.odometer,
   CASE
      WHEN
         COALESCE(Lag(e.accountingcode) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, so.odometer), 0) <> e.accountingcode 
THEN
   0 
ELSE
   Lag(so.odometer) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, so.odometer) 
   END
   AS 'Previous Odometer', so.odometer - Iif(( 
   CASE
      WHEN
         COALESCE(Lag(e.accountingcode) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, so.odometer), 0) <> e.accountingcode 
THEN
   0 
ELSE
   Lag(so.odometer) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, so.odometer) 
   END
) = 0, so.odometer, 
   CASE
      WHEN
         COALESCE( Lag(e.accountingcode) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, so.odometer), 0) <> e.accountingcode 
THEN
   0 
ELSE
   Lag(so.odometer) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, so.odometer) 
   END
) AS 'Miles Traveled', sh.hour, 
   CASE
      WHEN
         COALESCE(Lag(e.accountingcode) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, sh.hour), 0) <> e.accountingcode 
THEN
   0 
ELSE
   Lag(sh.hour) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, sh.hour) 
   END
   AS 'Previous Hours', sh.hour - Iif(( 
   CASE
      WHEN
         COALESCE(Lag(e.accountingcode) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, sh.hour), 0) <> e.accountingcode 
THEN
   0 
ELSE
   Lag(sh.hour) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, sh.hour) 
   END
) = 0, sh.hour, 
   CASE
      WHEN
         COALESCE(Lag(e.accountingcode) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, sh.hour), 0) <> e.accountingcode 
THEN
   0 
ELSE
   Lag(sh.hour) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, sh.hour) 
   END
) AS 'Hours Used' , fta.fifototalcost / fta.totalgallons AS [Avg Price], fta.totalgallons AS [Total Gallons], fta.fifototalcost AS [Total Cost]

The e.AccountingCode column contains the value that is at error.

Best Answer

The problem seems to be on the Coalesce's.

Returns the data type of expression with the highest data type precedence. If all expressions are nonnullable, the result is typed as nonnullable.

Source

For example:

select coalesce('101R',0)

Gives the error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '101R' to data type int.

This works fine:

select coalesce('101R','0')

Result:

(No column name)
101R

If the lag function on e.accountingcode can't be NULL, then you could use ISNULL(), but you should look into the differnces with coalesce.

Returns the same type as check_expression. If a literal NULL is provided as check_expression, returns the datatype of the replacement_value. If a literal NULL is provided as check_expression and no replacement_value is provided, returns an int.

Source

Or in other words, if the first value is not a NULL value, return that datatype, if it is NULL and a replacement value, in our case 0 is passed, then it will be that datatype. If all are NULL then it will be an integer.

Solution #1, changing 0 to '0'

SELECT
   e.accountingcode AS [Vehicle],
   Concat(e.year + ' ', e.make + ' ', e.model + ' ', Cast( e.[Description] AS VARCHAR(80))) AS NAME,
   Iif(cff.stringtype = '', cff1.stringtype, cff.stringtype) AS 'Operator',
   fta.fluid,
   so.odometer,
   CASE
      WHEN
         COALESCE(Lag(e.accountingcode) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, so.odometer), '0') <> e.accountingcode 
THEN
   0 
ELSE
   Lag(so.odometer) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, so.odometer) 
   END
   AS 'Previous Odometer', so.odometer - Iif(( 
   CASE
      WHEN
         COALESCE(Lag(e.accountingcode) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, so.odometer), '0') <> e.accountingcode 
THEN
   0 
ELSE
   Lag(so.odometer) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, so.odometer) 
   END
) = 0, so.odometer, 
   CASE
      WHEN
         COALESCE( Lag(e.accountingcode) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, so.odometer), '0') <> e.accountingcode 
THEN
   0 
ELSE
   Lag(so.odometer) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, so.odometer) 
   END
) AS 'Miles Traveled', sh.hour, 
   CASE
      WHEN
         COALESCE(Lag(e.accountingcode) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, sh.hour), '0') <> e.accountingcode 
THEN
   0 
ELSE
   Lag(sh.hour) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, sh.hour) 
   END
   AS 'Previous Hours', sh.hour - Iif(( 
   CASE
      WHEN
         COALESCE(Lag(e.accountingcode) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, sh.hour), '0') <> e.accountingcode 
THEN
   0 
ELSE
   Lag(sh.hour) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, sh.hour) 
   END
) = 0, sh.hour, 
   CASE
      WHEN
         COALESCE(Lag(e.accountingcode) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, sh.hour), '0') <> e.accountingcode 
THEN
   0 
ELSE
   Lag(sh.hour) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, sh.hour) 
   END
) AS 'Hours Used' , fta.fifototalcost / fta.totalgallons AS [Avg Price], fta.totalgallons

Solution #2, changing coalesce to ISNULL()

Educate yourself on the differences with coalesce, for example only do this when the LAG() function on e.accountingcode can't return NULL, which kind of defeats the purpose of the isnull 

SELECT
   e.accountingcode AS [Vehicle],
   Concat(e.year + ' ', e.make + ' ', e.model + ' ', Cast( e.[Description] AS VARCHAR(80))) AS NAME,
   Iif(cff.stringtype = '', cff1.stringtype, cff.stringtype) AS 'Operator',
   fta.fluid,
   so.odometer,
   CASE
      WHEN
         ISNULL(Lag(e.accountingcode) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, so.odometer), 0) <> e.accountingcode 
THEN
   0 
ELSE
   Lag(so.odometer) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, so.odometer) 
   END
   AS 'Previous Odometer', so.odometer - Iif(( 
   CASE
      WHEN
         ISNULL(Lag(e.accountingcode) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, so.odometer), 0) <> e.accountingcode 
THEN
   0 
ELSE
   Lag(so.odometer) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, so.odometer) 
   END
) = 0, so.odometer, 
   CASE
      WHEN
         ISNULL( Lag(e.accountingcode) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, so.odometer), 0) <> e.accountingcode 
THEN
   0 
ELSE
   Lag(so.odometer) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, so.odometer) 
   END
) AS 'Miles Traveled', sh.hour, 
   CASE
      WHEN
         ISNULL(Lag(e.accountingcode) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, sh.hour), 0) <> e.accountingcode 
THEN
   0 
ELSE
   Lag(sh.hour) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, sh.hour) 
   END
   AS 'Previous Hours', sh.hour - Iif(( 
   CASE
      WHEN
         ISNULL(Lag(e.accountingcode) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, sh.hour), 0) <> e.accountingcode 
THEN
   0 
ELSE
   Lag(sh.hour) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, sh.hour) 
   END
) = 0, sh.hour, 
   CASE
      WHEN
         ISNULL(Lag(e.accountingcode) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, sh.hour), 0) <> e.accountingcode 
THEN
   0 
ELSE
   Lag(sh.hour) OVER ( partition BY e.accountingcode 
ORDER BY
   e.accountingcode, sh.hour) 
   END
) AS 'Hours Used' , fta.fifototalcost / fta.totalgallons AS [Avg Price], fta.totalgallons
Related Question