September 17, 2018 at 8:36 pm
I've got a very complex CTE + union all query (view). I'm getting the error in the subject line.
I pretty sure why I'm getting the error - garbage data somewhere in my 40M rows. But until I find the column and row with the problem, I don't know the correct approach to fix the issue.
My question is...is there a way to determine which column is causing the error?
Short of that, I'll review the int columns in the view, select distinct the source rows, etc. But that's rather "tedious".
You'd think Microsoft could put the column name causing the error in the error message itself but nah, that would make too much sense 😉
September 18, 2018 at 6:32 am
Not easily... first order of business is good old divide and conquer. Since you have a UNION ALL, eliminate the 2nd half of it temporarily and see if that makes the problem go away. If that fails, put the 2nd half back and remove the first half. That will get you in a position to know which half of that is involved. If neither of those halves eliminates the problem, then it is somewhere else. Keep eliminating half the rows and when the problem goes away, you've found the area the problem is in. Once you can write a query that is a small subset of the whole that reproduces the error, then eliminate one column at a time until the problem goes away. Once you have the column, then select the raw data where the ISNUMERIC function fails on that column.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 18, 2018 at 12:01 pm
I'm guessing the error was about conversion to int, based on your comments, but it would be nice to be sure.
If so, run a pre-audit on the source data to find the bad row(s) and column(s). If it's a lot of columns, naturally you could generate the CASE and WHERE code rather than having to write it by hand.
SELECT
CASE WHEN column_3 LIKE '%[^0-9]%' THEN 'column_3,' ELSE '' END +
CASE WHEN column_4 LIKE '%[^0-9]%' THEN 'column_4,' ELSE '' END +
CASE WHEN column_6 LIKE '%[^0-9]%' THEN 'column_6,' ELSE '' END +
... AS bad_columns,
*
FROM dbo.source_table
WHERE column_3 LIKE '%[^0-9]%' OR
column_4 LIKE '%[^0-9]%' OR
column_6 LIKE '%[^0-9]%' OR
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 18, 2018 at 12:45 pm
If it's the result of an explicit conversion, you can replace it with a TRY_CONVERT/TRY_CAST as appropriate and then search for NULL values in the results. That won't help you if it's the result of an implicit conversion.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply