June 30, 2011 at 2:08 pm
Hello, i have the following SQL that is generating a conversion error. Any ideas why?
SELECT tl.line_id,
tl.line_object,
CONVERT(numeric(20,0),ISNULL(ISNULL(invalid_reference_no, reference_no),'0')),
RIGHT('00000000000000000000'+LTRIM(RTRIM(ISNULL(invalid_reference_no, reference_no))),20),
'?',
tl.reference_type
FROM
th,
tl,
ss,
mp
WHERE th.transaction_id = 123456789
AND th.transaction_id = tl.transaction_id
AND tl.line_void_flag = 0
AND th.store_no = ss.store_no
AND ss.media_parameter_table_no = mp.table_no
AND tl.line_object = mp.line_object
AND mp.verify_card_type_manual = 1
AND(datalength(tl.reference_no)<= 20 OR tl.invalid_reference_no IS NOT NULL)
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
If I add a FORCE ORDER hint, the error does NOT oocur.
Best Regards,
~David
June 30, 2011 at 3:10 pm
You can't guarantee the order of events in a query without FORCE ORDER, so what's happening is something is trying to get through the convert that you eventually weed out with your WHERE clause.
Your best bet when dealing with an overloaded column like this is to get a list of valid items first in a previous query, drop it to a #tmp, and then do your converts and whatnot against the valid list. It's a pain but it's the only way to be sure you won't run into the problem.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 30, 2011 at 11:36 pm
David Kranes (6/30/2011)
Hello, i have the following SQL that is generating a conversion error. Any ideas why? [...] If I add a FORCE ORDER hint, the error does NOT oocur.
Do not use FORCE ORDER to work around this sort of problem. The query should be rewritten so that whatever implicit or explicit conversion is failing cannot occur.
If you would be so kind as to provide CREATE TABLE statements for the tables concerned (so we can see the types) and perhaps a couple of rows of sample data for each (as INSERT statements please), I would be happy to spend 15 minutes or so on the rewrite, if that would help you.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply