0

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.

  • 1
    I'll go ahead and share the rest of the select statement and apologize ahead of time because it is a formatting nightmare. @RandiVertongen – gQuery Dec 20 '18 at 15:24
  • 1
    It looks like there may be some implicit conversion going on. Since AccountingCode appears to allow non-numeric data, what happens if you change your COALESCE statement to use '0' instead of numeric 0 like so - '0') <> e.AccountingCode – Scott Hodgin Dec 20 '18 at 15:31
2

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
  • 1
    Awesome! I had a feeling that the COALESCE function may have had something to do with it. I will definitely be educating myself on the differences! – gQuery Dec 20 '18 at 15:50
  • Great! Glad it helped, good luck :) – Randi Vertongen Dec 20 '18 at 15:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.