Wednesday, April 27, 2022

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.

5 comments:

  1. I wrote a version without using RPG


    Begin

    Declare Counter INTEGER DEFAULT 0;

    Drop TABLE MYLIB.FIRST_TABLE
    ;

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

    Loop_Tag:
    Loop

    Set Counter = Counter + 1 ;

    If Counter > 100 Then
    Leave Loop_Tag ;
    End If ;

    INSERT INTO MYLIB.FIRST_TABLE VALUES(LTRIM(TO_CHAR( Counter,'999G999')),
    INT(RAND()*100))
    ;

    End Loop Loop_Tag
    ;

    End
    ;

    ReplyDelete
  2. If you want to quickly create a random table with some random data, even with a single quick sql statement is possible, i.e. this creates a 4 field table, with random strings and random numbers and row id....

    CREATE TABLE MYLIB.MYTAB3 AS (
    WITH gen(id, data1, data2, data3) AS (
    (VALUES (0, 1, 1, 'STR'))
    UNION
    SELECT id+1, INT(RAND()*1000), INT(RAND()*100), 'STR'||CHAR(INT(RAND()*1000000))
    FROM gen WHERE id < 10000 --number of rows
    )
    SELECT * FROM gen WHERE id <> 0 ORDER BY id
    )
    WITH DATA

    ReplyDelete
  3. Hi Simon,

    You have great examples to learn the various aspects of SQL and RPG. Thank you for this.

    I believe there should not be an = sign after SET on line 08.

    Glenn

    ReplyDelete
    Replies
    1. Oops! Thank you for pointing that out. The correction has been made.

      Delete
  4. That was an interesting one. It's a bit RBAR with those loops or does it goes in some BATCH Insert Mode?
    Nice would be to have a standard Table function, something like PostgreSQL `generate_series()`.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.