Re: AW: AW: Is this me or another SQL bug?

On 06-Jun-2010 02:03, Birgitta Hauser wrote:

FWiW, that expression is only sufficient to determine which
character field values do not represent valid _positive_
integers; i.e. valid character representation of negative
integer values would be selected by that predicate. If the
character field includes a left-negative sign for a valid
integer value, the [implicit] cast to numeric would occur
without error.

... and you forgot that sometimes decimal dots and/or decimal
commas could be used, and a plus or minus sign could be used on
the right or left hand, instead of a minus sign the numeric value
can be enclosed within parenthesis. Sometimes also a dot or a
comma is used to separate the digits on the left hand of the
decimal comma/dot (for example 1.234.567,89 or 1,234,567.89).
You'll see all those presentations in the same column/field if
you get an SAP interface (at least I saw it in that one I got).

... and that's why I have an RPG function that checks all those
representations. An other RPG function converts the checked
values into a numeric value. Both functions are registered as
UDFs and used in SQL.

Not forgotten, purposely ignored. While those variants are valid additional concerns, generically, for various representations of numeric values in character form,...

As the original issue was for incorrect representation for the expression "int(T2.characterField) = T1.numericField", I figured the only interest was in detecting whatever characterField values would not be valid for the INT() scalar cast function. Anything beyond blanks as suffix and\or prefix, plus optionally a floating left negative [minus] symbol, and additionally only digit characters, would not be a true integer value.

Had the OP implied that rounded\truncated decimal values were allowed, only then would I have considered that a decimal separator needed consideration. A thousands separator is never allowed by the SQL for cast, so that was not even considered without explicit mention. Those and any other various representations of numeric values in character form are moot anyhow, as all would have already been diagnosed as invalid by the original expression [and by those variants I had suggested for allowing the left-minus]. So if the OP had issues for any non-integer or unsupported numeric representations, those values would have been selected\presented in the given query. My goal in replying was that the OP should not also see _valid negative_ representations presented as invalid integer representations. :-)

Regards, Chuck