Search code examples
sqlsql-servert-sqlcastingsql-server-2019

Error converting nvarchar to bigint in WHERE clause fails but works in SELECT

I am trying to achieve the following:

Get all Field values where the FieldValue is greater than 100 when the value is stored as a number.

The indicator of whether or not if a value was stored as a number is given by the field type, which is another where clause.

The issue I am facing is that when I try to do the data field conversion in my WHERE statement, it failed.

I run:

SELECT FieldValue FROM CARData A
JOIN Fields B ON A.FieldId = B.FieldId
WHERE FieldTypeId = 3 AND FieldValue IS NOT NULL

And this returns the expected result of:

Result

But if I added the WHERE clause to filter by the value:

SELECT FieldValue FROM CARData A
JOIN Fields B ON A.FieldId = B.FieldId
WHERE FieldTypeId = 3 AND FieldValue IS NOT NULL 
AND CAST(FieldValue AS BIGINT) > 100

It throws the error:

Error converting data type nvarchar to bigint.

I somewhat understand what the issue is- it is trying to convert ALL values in the table to a bigint and is failing when it hits a non-numeric value.

I attempted to solve this by nesting the first query in a second like so:

SELECT RESULT.FieldValue FROM (
SELECT FieldValue FROM CARData A
JOIn Fields B ON A.FieldId = B.FieldId
WHERE 
FieldTypeId = 3 
AND FieldValue IS NOT NULL 
AND ISNUMERIC(A.FieldValue) = 1) RESULT
WHERE CAST(FieldValue AS BIGINT) > 100

But even that does not return anything other than the aforementioned error.

Solution

  • While it's true that changing the structure of your query can cause SQL to pick a different plan, you should limit that technique to trying to help the optimizer pick a performant plan. The reason for this is that if successful execution of the logic depends on a particular choice of plan, then your query might work today but fail tomorrow (or in production) when SQL decides to pick a different plan.

    Fortunately you don't have to rely on that here! Use try_cast

    SELECT FieldValue FROM CARData A
JOIN Fields B ON A.FieldId = B.FieldId
WHERE FieldTypeId = 3 
AND TRY_CAST(FieldValue AS BIGINT) > 100

    I'm also curious... is this part of a SQL course? If so, tell your instructor to come visit StackOverflow so we can tell them to stop teaching students to use EAV's. Unless the whole point is to show you how horrible they are! :)