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

[CRPence <CRPbottle@xxxxxxxxx>]

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