February 1, 2011 at 11:12 am
I have a statement that causing problems and to test what's going on I've ended up with the following code. If I highlight the select statement it returns nothing (that's fine I've swapped the isnumeric =1 for isnumeric = 0 for purpose of test). What I cannot understand is that if I run the whole statement including the insert I get the error.
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'PDR App' to data type smallint.
How can an insert of nothing create a conversion error. If it were in the select surely it should error then as well?
Insert into Target_Table
(emp_no, col2,
col3,
date_from,
date_to)
select P.emp_no, A.a_col1 as col2,
A.a_col_desc as col3,
dbo.Conv_Valid_Dates(P.Date_From) date_from,
dbo.Conv_Valid_Dates(P.Date_To) date_to
FROM Table1 P
inner Join Table2 F
on F.ID_Num = P.ID_Num
and F.ID_Name ='history'
and dbo.Conv_Valid_Dates(P.Date_From) is not null
inner JOIN Table3 AS A
on P.Criteria_ID = A.Criteria_ID
and isnumeric(A.a_col_desc) = 0
February 1, 2011 at 11:31 am
It has to do with the sequence in which SQL Server does things.
First, it tests to make sure it can compile the code. Then it tests to make sure the data will "fit" in the target (includes tests for size and data type/conversions). Then it tries to actually run it.
The pieces of the Where clause actually get tested late in the chain, which is why IsNumeric isn't generally suitable for this purpose. The usual solution is to first insert into a temp table, using IsNumeric and all that, then to insert from there into the target table, with data that's already been filtered.
Edit: And, yes, it's stupid that it works that way and has to be dealt with in that kind of fashion. Violates the whole purpose of a declarative language like T-SQL. So learn the technique, and ask MS to fix it, just like everybody else does.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 1, 2011 at 11:36 am
GSquared is correct. The order of operations has the insert tested and you have a type conversion, without data being moved.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 1, 2011 at 11:49 am
Or plan B not using the temp table and saving some overhead.
DECLARE @tst VARCHAR(100)
SET @tst = 'fail'
SELECT 1 WHERE NOT @tst LIKE '%[^0-9]%'
SET @tst = '0123456789'
SELECT 1 WHERE NOT @tst LIKE '%[^0-9]%'
This is the equivalent of IsAllDigits which will work. However you'd have to edit it to accept periods, commas or other acceptables characters.
February 2, 2011 at 6:39 am
Ninja's_RGR'us (2/1/2011)
Or plan B not using the temp table and saving some overhead.DECLARE @tst VARCHAR(100)
SET @tst = 'fail'
SELECT 1 WHERE NOT @tst LIKE '%[^0-9]%'
SET @tst = '0123456789'
SELECT 1 WHERE NOT @tst LIKE '%[^0-9]%'
This is the equivalent of IsAllDigits which will work. However you'd have to edit it to accept periods, commas or other acceptables characters.
The problem with a test like that is, add in periods so that 9.5 is a number, and you also end up with 192.168.0.1 as a "number". You have to test more thoroughly than that if the data can contain number-based strings. Of course, IsNumeric has similar caveats, in that you might have problems performing mathematical operations on "1D6", but IsNumeric will call that a number (it's shorthand for 1-million).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 2, 2011 at 7:59 am
Tx G... so I guess a small insert hit is not that big a deal after all :hehe:.
February 2, 2011 at 8:03 am
Thank you all for your replies. With this information we've managed quite a simple work around by first loading the data into a new varchar column and then sorting out the resulting data at a second stage - it was a workfile for this process anyway so changing the order isn't a major deal.
I am now a little more educated
February 2, 2011 at 8:05 am
It depends on how well you know the data. If it can only fit certain patterns, you can code against those. You have to assume then that the patterns are immutable for all time, or that your error checking will prevent problems in the future.
Outside of that, I've found the temp table staging solution to be the most reliable. It's a trade-off of lower speed for higher reliability.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 2, 2011 at 8:10 am
Totally agree GSquared,
as I say the table we're inserting into in this case is effectively a temp table within a datamart process and the join is pretty much a data cleansing issue anyway so probably it's better that we've changed it and made the process more transparent. It wouldn't always be appropriate.
February 2, 2011 at 8:12 am
Do you have a link to the connect ticket? I'm searching on sql 2008 and I'm not finding it!!
February 2, 2011 at 8:20 am
Ninja's_RGR'us (2/2/2011)
Do you have a link to the connect ticket? I'm searching on sql 2008 and I'm not finding it!!
Is this the one G?
https://connect.microsoft.com/SQLServer/feedback/details/177308/enhancement-to-isnumeric
February 2, 2011 at 8:25 am
Ninja's_RGR'us (2/2/2011)
Ninja's_RGR'us (2/2/2011)
Do you have a link to the connect ticket? I'm searching on sql 2008 and I'm not finding it!!Is this the one G?
https://connect.microsoft.com/SQLServer/feedback/details/177308/enhancement-to-isnumeric
That one's a good start.
The main complaint I have, though, is that telling SQL "Where IsNumeric(Column) = 1" still giving a conversion error, because of the sequence of operations, violates the purpose of a declarative language. It leaves us using procedural methods to handle what should be a fairly simple operation. It comes up frequently enough on these forums that it needs to be addressed.
I don't have a link to a connect ticket for that. I've seen one, but it was a long time ago.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 2, 2011 at 8:30 am
Care to make one (I can't find a better match)? I think you'd explain it better than me.
Let me know either way and I'll vote for it.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply