AW: Is this me or another SQL bug?

Hi,

there are invalid numeric values in the character column that cannot be
converted correctly.

instead of converting the character value into an integer value I'd convert
the numeric value into a character value:

Select * From T1, T2
WHERE Trim(T2.CharacterField) = VarChar(T1.NumericField)
;

To test for invalid numeric values you may run the following query:

Select CharacterField

From Table

Where Length(Trim(Translate(CharacterField, ' ', '1234567890'))) >
0
;;

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von David FOXWELL
Gesendet: Thursday, 03. June 2010 11:57
An: Midrange Systems Technical Discussion
Betreff: Is this me or another SQL bug?


Ok the odds that it's a bug instead of me must be a million to one, but as
we've now had 2 bugs fixed I thought I'd ask.

In T2, characterfield contains numbers and letters :

SELECT * FROM T1, T2 WHERE
int(T2.characterField) = T1.numericField

Erreur de sélection ou d'omission dans la zone Cast(T2.characterField AS
Integer) du membre T1.

I hope the message is easily readable, I didn't want to translate and give a
message that was not exactly the same as the English version. Not sure that
I understand the reference to T1. The error is because of int('ABCetc').


Now, by adding T2.Type I can select only characterfield values that I know
contain only numbers, so this works :
SELECT * FROM T1, T2 WHERE
int(T2.characterField) = T1.numericField AND T2.Type = 'A'


My question is why should I need to do this? I have only asked for records
where int(T2.characterField) is equal to to T1.numericField so shouldn't
those that provoke the error be ignored? It would seem that the Int is not
performed on all the records in the 2nd statement.

Thanks