Modulo operator

[Guest.Visitor]

I don't think there is a MOD operator in QUERY/400. I've come up with a couple of workarounds, though. a) Define your query as far as you can, then RTVQMQRY, and edit the source to use the SQL/400 MOD operator. Run it with STRQMQRY. b) Use the following calcs with rounding turned off. Let's say you have field A = 12345 and B = 7; you want to find 12345 MOD 7 = 4: 1. Turn off rounding: select 'Specify processing options' and change 'Use rounding' to N. 2. Define INTFLD as A / B with length 7 0 (or whatever's appropriate). INTFLD is then 1763 3. Define DIVFLD as A / B with length 31 15. DIVFLD is then about 1763.571428571428. 4. Define RESULT as ((DIVFLD - INTFLD) * B) + 0.5 with length 7 0. RESULT is then 4. Turning off rounding may affect other results, but it can be simulated by adding 0.5 to results and truncating by how you define the length. Division with dollar-and-cents amounts can be rounded this way by adding 0.005 to results before truncating to 2 decimal places.

[Guest.Visitor]

Thanks, Ken. Option (b) is quite creative, that's probably the one I'll try first. I've used QMQRY a little bit, is the MOD operator supported if you don't have SQL/400 installed?

[Guest.Visitor]

Your steps 3 and 4 can be replaced by: 3. Define RESULT as A - (B * INTFLD) with length 15 0, or whatever. This is equivalent to your original method for zero and positive A, but gives different results for negative A. (Both methods need some adjustment to deal with negative input.)

[davida@yahoo.com]

mmmmmmmmmm

[Guest.Visitor]

Anyone know if a modulo operator exists that can be used in "Define Result Fields" in AS400 query definitions? (I'm aware of the RPG %rem bif.)

[Guest.Visitor]

The MOD operator should be supported. SQL/400 gives you different ways to use the existing SQL engine, more or less.