Query/400 Does Exponentiation, Sort Of

June 8, 2005 Ted Holt and Bob Ellsworth

Dear Colleagues:

Query/400, the software IBM never got around to completing, does not have an exponentiation operator, but that did not stop Bob Ellsworth from making Query carry out exponentiation. If you’ve seen Bob’s tips before, you may remember that Bob is the guy who makes Query do what it was never designed to do. Here’s the technique Bob shared with me to answer a question from a reader.

–Ted

I am new to IBM and Query/400 and have a problem with a result field. I am working with two pieces of data:

1. Cost with no decimals (e.g., 300)

2. Division factor (e.g., 2)

The division factor is the power of 10 by which the cost should be divided to get the true cost. For example, here is the cost 300 adjusted for factors of 2 and 3.

300/(10^2) = 3.00
300/(10^3) = .300

How can I make Query divide by the power of 10 indicated by the division factor?

–Jack

You came up with a good one. I love the ones that look very easy to answer, until I go to test it and find that there really is no exponential function.

Create a fixed table of factors, from which you can substring out the correct multiplier based on the division factor.

Field	Expression	Len	Dec
FACTORTBL	‘00000100001000010000100001000010
	0000′
F	microsecond(timestamp(‘2005-01-01
	-12.00.00.’||substr(factortbl,fac
	tor*6+1,6)))
ADJCOST	Cost / f	13	7

Output looks like this:

COST   FACTOR          ADJCOST
 300      0        300.0000000
 300      1         30.0000000
 300      2          3.0000000
 300      3           .3000000
 300      4           .0300000
 300      5           .0030000                 

This technique is a little limited, though, in that the division factor can only range from 0 to 5.

–Bob Ellsworth

I’ll add just a few words to Bob’s solution.

First, if you want to allow the division factor to range up to 9, add a second table.

Field	Expression	Len	Dec
FACTORTBL1	‘00000000000000000000000000000000
	0000000001000010000100001000′
FACTORTBL2	‘00000100001000010000100001000010
	0000000000000000000000000000′
F1	microsecond(timestamp(‘2005-01-01
	-12.00.00.’||substr(factortbl1,fa
	ctor*6+1,6)))
F2	microsecond(timestamp(‘2005-01-01
	-12.00.00.’||substr(factortbl2,fa
	ctor*6+1,6)))
ADJCOST	cost / (f1 * 1000000 + f2)	15	9

Here’s the output:

COST   FACTOR            ADJCOST 
 300      0        300.000000000 
 300      1         30.000000000 
 300      2          3.000000000 
 300      3           .300000000 
 300      4           .030000000 
 300      5           .003000000 
 300      6           .003000000 
 300      7           .000300000 
 300      8           .000030000 
 300      9           .000003000

The second thing I’ll say is that this example is just one more reason to dump Query/400 and get a decent query package. There are some good ones on the market.

–Ted