Comparison operator

[anoobbhaskar]

I have a user table with one field (user-id) I have defined as character. Currently all the values in this field are numeric and I am able to get correct result while I execute the below query. Can you please tell me why progress is behaving like this? Is the below query is correct?

for each <table name> where <table name>.user-id <= '100' :

display <table name>.user-name.

end.

It is correctly displaying all user names which has an user-id less than 100

 

[Cringer]

What's the problem? You say it's returning what you want.

 

[TomBascom]

It looks like the 'id' field is defined as character but just happens to really have integers in it? If our original poster is hoping that '90' will sort less than '100' he is doomed to disappointment. If you want numerical sorting define the field as a number or convert it - I.e. WHERE INTEGER( user-id ) < 100 but be aware that conversion in the WHERE clause will result in a table scan and poor performance.

 

[anoobbhaskar]

Thanks for the reply Tom.
My question is why progress is still displaying the user id's less than 100 though the field has defined as character? I assume its because of the field contains only integer values and I just want to know whether its an issue or not?

It looks like the 'id' field is defined as character but just happens to really have integers in it? If our original poster is hoping that '90' will sort less than '100' he is doomed to disappointment. If you want numerical sorting define the field as a number or convert it - I.e. WHERE INTEGER( user-id ) < 100 but be aware that conversion in the WHERE clause will result in a table scan and poor performance.

 

[anoobbhaskar]

I would like to know the whether the above mentioned example is an issue or not. The field has defined as character varaible and it contains numeric values in it. Progress will execute the above query and display the results properly with character field (where <table name>.user-id <= '100') .

 

[Rob Fitzpatrick]

You have defined this field as character, so these values are not numeric. They are strings, and will be compared as such.

This means '099' is less than '100' and '100' is less than '101', for the same reason that 'abb' is less than 'abc' which is less than 'abd'. However it is also true that '100' is less than '2'. These are string comparisons, not arithmetic comparisons of integers converted from strings.

This isn't "an issue". This is how comparisons work with character values. If you want numeric comparisons, use a numeric data type. Either store your values as numeric values (int, int64, decimal), or convert them at run-time, but be prepared to deal with values that are non-numeric, like 'xyz'.

 

[anoobbhaskar]

Thanks for the reply Rob.