Placing SQL functions into the SQL Insert statement

To write the posts for this website I need to have examples DDL tables. I often use a Table whose first column contains the row number and a second contains some kind of random number.

While I was illustrating how to fill one of these tables it struck me that there was an easier way to do this, having all the logic in the Insert statement itself.

This is the source code for the Table I commonly use:

01 CREATE TABLE MYLIB.FIRST_TABLE 02 FOR SYSTEM NAME "TABLE1" 03 (FIRST_COLUMN FOR COLUMN "FIRST" VARCHAR(10), 04 SECOND_COLUMN FOR COLUMN "SECOND" SMALLINT, 05 UNIQUE (FIRST_COLUMN)) ;

I always create my DDL Tables with a long name, FIRST_TABLE, and a short system name, TABLE1.

Lines 3 – 4: The Table contains two columns:

1. FIRST_COLUMN:  This will contain the value from the counter that was used to count the number of rows written to the table. I always set this to be a VARCHAR type column, and I format the "number" contained within with thousand separators ( , ).

2. SECOND_COLUMN:  This column contains the random number.

Line 5: I always have liked having my DDS files and DDL Tables with a unique key.

I decided to use the SQL RAND function to generate the random, just because it is simpler to code than a call to an API to do the same.

I want to have my random numbers as integers. But RAND() does not return integers. The following statement illustrates what it does, and how I generated my integer:

VALUES RAND(),RAND()*100,INT(RAND()*100) ;

The results look strange until you realize each random number will be different as a different random number is generated each time I use RAND().

00001 ------------------ 0.2736899929807428 78.30439161351359 98.0

The first row of the results is from using just RAND(). If I was to convert this number, and many of the other values returned, to an integer most of my results would be zero.

Second row shows what happens when I multiply the random number by 100. Now I get a possible three numbers before the decimal place. But integers do not have decimal places.

In the third row I use the INT function to convert the number generated by RAND() * 100 to an integer value.

I think most of us would write the RPG program to populate this Table in a manner similar to this:

01 **free 02 dcl-s Counter uns(10) ; 03 dcl-s Col1 char(10) ; 04 dcl-s Col2 uns(3) ; 05 exec sql SET OPTION COMMIT = *NONE ; 06 for Counter = 1 to 500000 ; 07 Col1 = %triml(%editc(Counter:'J')) ; 08 exec sql SET :Col2 = INT(RAND()*100) ; 09 exec sql INSERT INTO FIRST_TABLE VALUES(:Col1,:Col2) ; 10 endfor ; 11 *inlr = *on ;

Line 1: If its RPG in 2022 is has to be free (format).

Line 2: This variable will be used to count the number of inserts to the Table.

Lines 3 and 4: These variables will contain the values to be inserted into the Table.

Line 5: I use the SET OPTION to ensure the commitment control is not used.

Line 6: Start of the For-group that will be performed 500,000 times.

Line 7: Use the %EDITC BiF to convert the number in the variable Counter to a formatted character string. The "number" is right justified in the string, therefore, I use the %TRIML to remove the leading blanks to left justify the string.

Line 8: As I mentioned earlier I am using the INT function to convert the result of the RAND() * 100 calculation into an integer value.

Line 9: I am inserting the values in the Col1 and Col2 variables into the Table.

Then I had a thought: I could code both of those columns as part of the Insert statement, and not need those extra variables, etc.

The new version of the program looks like:

01 **free 02 dcl-s Counter uns(10) ; 03 exec sql SET OPTION COMMIT = *NONE ; 04 for Counter = 1 to 500000 ; 05 exec sql INSERT INTO FIRST_TABLE VALUES(LTRIM(TO_CHAR(:Counter,'999G999')), 06 INT(RAND()*100)) ; 07 endfor ; 08 *inlr = *on ;

Line 2: Only one variable needed now.

Line 5: Here I do the same as I did for the variable COL1 in my previous example program. I have the variable Counter and I am converting it to a character string using the TOCHAR function, notice that the functions second parameter is the format I want the character in where "G" indicates a comma ( , ). This is within a LTRIM function, that left trims the character string.

Line 6: Exactly the same SQL statement I used with the SET in the previous example.

After compiling the program and executing, I want to see my results:

SELECT * FROM FIRST_TABLE LIMIT 10 ; FIRST_COLUMN SECOND_COLUMN ------------ ------------- 1 1 2 32 3 15 4 5 5 34 6 63 7 97 8 74 9 51 10 7

I have only returned the first 10 results.

Let me go to the "other end" of the file, the last five rows.

SELECT * FROM FIRST_TABLE ORDER BY 1 DESC LIMIT 5 ; FIRST_COLUMN SECOND_COLUMN ------------ ------------- 999 93 998 53 997 33 996 35 995 94

That did not return to me the results I expected. As FIRST_COLUMN is character then the value "999" is greater than "500,000". But if I sort the Table using its Relative Record Number, RRN, I get what I need.

SELECT * FROM FIRST_TABLE A ORDER BY RRN(A) DESC LIMIT 5 ; FIRST_COLUMN SECOND_COLUMN ------------ ------------- 500,000 73 499,999 53 499,998 55 499,997 36 499,996 16

I get the result I desire, the last five rows of the Table.

 

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