February 6, 2022 at 12:35 pm
Hi,
We are running SQLSERVER 2016.
We have a process that has been running fine for years that this month has run into an issue.
There is a simple select statement that links 3 tables.
SELECT count(*)
FROM TABLE_A A
INNER JOIN TABLE_B A
ON A.X = B.X
INNER JOIN TABLE_C
ON B.Y = C.Y
This select still runs fine and returns the records as expected.
Normally there is a where clause on the end:
WHERE C.NVACHAR_FIELD <> 'SomeTextString'
This has worked without issue for years but this month has failed throwing:
'Error converting data type varchar to numeric.'
The where clause is comparing a nvarchar field with a simple three character text string, no numerics to be seen...
There are no numeric values in C.NVACHAR_FIELD
The odd thing is that the error message is displayed no matter what the where clause is or which field it relates to even something like where C.cust_ID = 123
I have tried re-creating the table under a new name and filling with data from the original table just in case there was something corrupted in the table (grasping I know) but same error is displayed.
Has anyone seen anything behaviour like this before?
Cheers..
Shaunos
February 6, 2022 at 1:40 pm
... has failed throwing:
'Error converting data type varchar to numeric.'
The where clause is comparing a nvarchar field with a simple three character text string, no numerics to be seen...
Shaunos
Cheers Shaunos. The error says the conversion is varchar to numeric while the column type for comparison is nvarchar. To make sure the comparison is type safe you could try adding the unicode prefix (N) to the string
WHERE C.NVACHAR_FIELD <> N'SomeTextString'
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
February 6, 2022 at 2:50 pm
Thanks for the suggestion Steve but no joy I'm afraid, still the same error..
Cheers,
~Shaunos
February 6, 2022 at 2:52 pm
Has anyone seen anything behaviour like this before?
Yes... and every time someone claims that something has been running for years and no one has made a change, someone has changed something somewhere. The only way that we could help you find it is for you to post all of the DDL involved, the DML being used without modification (the code you posted as an example is actually incorrect even as an example), and all of the data. You're probably not going to do that (and I don't blame you there in this case) and so my recommendation would to to verify everything hand over fist, skipping nothing and making no assumptions because a change somewhere was made and it could be in the data itself.
--Jeff Moden
February 6, 2022 at 3:45 pm
If you look at the code sample you said didn't work you would find that it couldn't even run. If you provide sample data in create table statements you will probably sort out the problem yourself
February 6, 2022 at 3:57 pm
Hi Jeff,
Thanks for your reply and I now see the typo in the table alias.... duh!
As it happens, I think we have just figured out the issue which seems to be data driven.
One of the joins in the statement does a cast(Field as decimal(15,0)), but this month one of the values for Field in the imported table has garbage in it.
Interestingly the select statement returns all the rows without error , it is only when we add a where clause that it throws the error for the failed cast statement.. this is what was throwing us in the wrong direction..
Cheers,
~Shaunos.
February 6, 2022 at 5:18 pm
It always happens that way with such "no one made any changes" such things. 😀
It would be nice if you could post the actual code you're talking about so we could see for ourselves.
--Jeff Moden
February 6, 2022 at 5:24 pm
It always happens that way with such "no one made any changes" such things. 😀
It would be nice if you could post the actual code you're talking about so we could see for ourselves.
Ideally, along with sample data which allows us to recreate this 'odd thing'.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 6, 2022 at 5:38 pm
That is also one of the problems with creating a 'sample' query that isn't the same as the actual query being executed. If you had posted the original query as-is (maybe obfuscating table/column names) - we would have seen that cast and immediately looked to that as part of the issue.
The reason why adding the WHERE clause causes the error to be generated - but doesn't without the WHERE is because SQL Server is probably using a different index and different order of operations. Without the WHERE it never hits the 'bad' row...
There are several ways around the issue, but the best way is to fix the system so it doesn't allow garbage data. Since that is going to be an uphill climb and probably out of your control - the next best option is a computed column, persisted and indexed using: TRY_CAST(column AS decimal(15,0)). Now you can join to the computed column and avoid the issue because any values that cannot be cast will be NULL and eliminated from the join.
The next best option would be to use TRY_CAST in the join - and again, anything that cannot be cast will be excluded.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
February 7, 2022 at 8:36 am
Hi Jeff,
Yes in hindsight I should have shown the exact select statement which would have shown the cast as part of the join. I guess I was thrown by the fact that the select statement on its own was working...
The try_cast does remove the error but that just hides another issue in that the third party has delivered bad data to us.
In fairness, as I mentioned before, these files have been fine for years except for this month but yes we will have to add some more defensive checks to the data once loaded to cover this potential crack going forward.
Everyday's a school day!! 🙂
Thanks again..
~Shaun.
February 7, 2022 at 7:34 pm
In fairness, as I mentioned before, these files have been fine for years except for this month but yes we will have to add some more defensive checks to the data once loaded to cover this potential crack going forward.
Everyday's a school day!! 🙂
You said a mouthful there. I don't believe there's a person on this thread that hasn't been burned by changes in data that are beyond our control and I believe you've just stated the unspoken mantra for us all. 😀 Welcome to the "Wanna Bet?" club. 😀
--Jeff Moden
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply