max(char10) is giving wrong data where column has alphanumeric data

[ramahanumanth]

Hi,

I have a table with column char(20). The table has alphanumeric data. When i select max(column) on that table, its giving wrong data. See below more details:

Data:
db2 "select * from EPSAR_WKLY_DET_ UV"
R33337A00A3E
R33337883E70

select max(RETAIN_USER _ID) from EPSAR_WKLY_DET_ UV" =
R33337A00A3E

Instead of R33337883E70 which is bigger, its displaying
R33337A00A3E.
DB cfg parameters:

Database code page= 1208
Database code set = UTF-8
Database collating sequence= IDENTITY

Can anyone help with how to fix this issue.

Regards,
Ramz.

[Rabbit]

R33337A00A3E is larger than R33337883E70 because A is larger than 8. All alpha characters are larger than numeric characters. You will have to strip the field of all other characters before doing your max.

[ramahanumanth]

When i ran the query against production database, I got R33337883E70 as maximum value and i don't have access to production server to check dd cfg parameters.
What needs to be changed to get the other value as maximum just like production database.

Thanks.

[vijay2082]

Hi,

You are getting correct result as per your settings.Get your DBA into picture and tell him to cross check the things for you. Rather than speculating the things; that can be straight forward way to nail the issue and get the things going.

Cheers, Vijay