2 - Is there any way you can fix the equation in SQL? I don't
know if it's applicable but moving stuff from the left to the
right of a where clause can help performance by using indexes (if
available) but can also avoid errors. For example, a coworker
used to use where NumToDate(numericdate) = current date and
changing that to where numericdate = DateToNum(current date)
solved the issue with invalid dates (we also changed NumToDate to
return a null on an invalid date).
Bad data is bad data. Data access method is chosen by the SQL,
not the user. Influencing the plan does not ensure the plan.
Simply... A change to the query that "avoids" the condition for one
access, may not hold for the next invocation of the same SQL due to
change in access plan. The *only* fix to avoid decimal data errors
[as exposed by an SQL query] is to correct the data [or in the very
unlikely case, to obtain the fix for an improper diagnosis of data
being bad when in fact the data is valid].
3 - Expanding further on functions, could you pass the numeric
field to a function to pass back a number? select ...,
From ... ?
Bad data passed to a function is still bad data, and a decimal
data error is expected; i.e. the function can not act on the bad
data any more than can selection or collation. Only passing the
"raw" data to the function should be sure to avoid the error. The
only "function" which enables exposing the "raw" data to the query
is the HEX() function.
However the hex representation is pseudo-raw rather than simply
binary and not generally usable; e.g. see a recent message in the
archives for detecting bad zoned decimal using HEX(), and the much
easier method for packed in other messages [in the same thread and
other older archive messages and elsewhere] as in:
This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact