Re: Canonical list of QAQQINI options?

rob@xxxxxxxxx wrote:

<<SNIP>>

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 ..., MyRpgFunctionToReturnANumberFromDecimalDataErrorField(PotentiallyBadNumber)
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.
http://archive.midrange.com/midrange-l/200907/msg00403.html
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:
http://archive.midrange.com/midrange-l/200907/msg00517.html

Regards, Chuck