Calculating the power and square root of a number

There are times I need to calculate the dimensions of objects, and to do that I need to use things like x squared, or the square root of y. It all brings back memories of sitting in mathematics lessons back in high school, a very long time ago.

It is simple in RPG, but I want to do it in SQL so that I can calculate the information I need in my SQL View.

I thought it would be a good reminder to show how to calculate the square root and how to use exponentiation (x^y, x to the power of y) in both RPG and SQL.

 

Square root and exponentiation with RPG

I might as well dive in and show my example program:

01 **free 02 dcl-s Number packed(5 : 0) inz(5) ; 03 dcl-s Result packed(9 : 2) ; // Exponentiation 04 Result = Number ** 2 ; 05 dsply ('1. ' + %char(Result)) ; 06 Number = Result ; // Square root 07 Result = %sqrt(Number) ; 08 dsply ('2. ' + %char(Result)) ; 09 reset Number ; 10 Result = 2 ** Number ; 11 dsply ('3. ' + %char(Result)) ; 12 *inlr = *on ;

Line 2 and 3: Definition of the packed numeric variables this program will use. Notice that Number, line 1, is initialized with the value 5.

Line 4: I "square" the number held in the variable Number. The two asterisks are used denote "to the power of". In this example the contents Number, 5, is the base and 2 is the exponent.

Line 5: Display the result.

Line 6: Move the value from Result into Number.

Line 7: Use the square root BiF to calculate the square root of the value in Number.

Line 8: Display the result.

Line 9: The RESET operation code resets the value of a variable to what it was at program initialization. As I used the INZ keyword in the definition, Number is reset to the value 5.

Line 10: Here 2 is the base and the contents of Number is the exponent.

Line 11: The result is displayed.

After compiling, when I called the program the following was displayed:

DSPLY 1. 25.00 DSPLY 2. 5.00 DSPLY 3. 32.00

First display is the result of 5 squared.

Second is the square root of 25.

Third is 2 to the power of 5.

 

Square root and exponentiation with SQL

There are two functions that I will be using in these examples:

1. POWER:  This has two variables the base and the exponent
2. SQRT:  Has one variable, the number I want to calculate the square root of

A quick example of these functions would be:

01 VALUES (POWER(2,2),SQRT(POWER(2,2)))

Which returns:

00001 00002 ------ ------ 4.0 2.0

The first column result was generated by the POWER function, showing what 2 squared is. In second column I squared 2 and then used SQRT to return the square root of the result.

To give a more "real world" example I created a file, TESTFILE, with a packed numeric field in it called NUMBER. I then used the following SQL statement to show how these functions work.

01 SELECT NUMBER, 02 POWER(NUMBER, 2) AS "Squared", 03 SQRT(NUMBER) AS "Square root" 04 FROM TESTFILE

Line 1: The value in column Number will be displayed.

Line 2: Using the POWER function the square of the value in Number will be returned.

Line 3: With SQRT the square root of the number is returned.

This produced the following results:

NUMBER Squared Square root ------ ------- ----------------- 4 16.0 2.0 9 81.0 3.0 15 225.0 3.872983346207417

In the final example I am going to use the value in Number as the exponent in POWER:

01 SELECT NUMBER, 02 POWER(2, NUMBER) 03 FROM TESTFILE

Line 2: Here I am using the value in NUMBER as the exponent of 2.

The results are:

NUMBER 00001 ------ ------- 4 16.0 9 512.0 15 32768.0

Now I can take POWER and SQRT and include them in my SQL Views, and have one less thing the RPG has to calculate.

 

You can learn more about this from the IBM website:

• SQL POWER function
• SQL SQRT function

 

This article was written for IBM i 7.6, and should work for some earlier releases too.