It s not comparing 08 with 20 , it is, as you expect, comparing 0800 with 20 .
What you don t seem to expect, however, is that 0800 (the string) is indeed less than 20 (the string).
If converting it to numerics for a numeric comparison is out of the question, you could use the following DB2 function:
right ( 0000000000 ||val,10)
which will give you val padded on the left with zeroes to a size of 10 (ideal for a CHAR(10), for example). That will at least guarantee that the fields are the same size and the comparison will work for your particular case. But I urge you to rethink how you re doing things: per-row functions rarely scale well, performance-wise.
If you re using z/OS, you should have a few DBAs just lying around on the computer room floor waiting for work - you can probably ask one of them for advice more tailored to your specific application :-)
One thing that comes to mind in the use of an insert/update trigger and secondary column PRI_CODE_PADDED to hold the PRI_CODE column fully padded out (using the same method as above). Then make sure your PriCode variable is similarly formatted before executing the select ... where PR_CODE_PADDED < PriCode.
Incurring that cost at insert/update time will amortise it over all the selects you re likely to do (which, because they re no longer using per-row functions, will be blindingly fast), giving you better overall performance (assuming your database isn t one of those incredibly rare beasts that are written more than read, of course).
