For groups in CL

This comes under the category of: I thought I had already written about this. The DOFOR command in CL allows me to construct a For group in CL, like I can in RPG when I use its FOR operation code. The same information has to be given to both:

RPG === for Count = 1 to 10 by 1 ; CLLE ==== DOFOR VAR(&COUNT) FROM(1) TO(10) BY(1)

1. What I call the count variable
2. Starting value
3. End value
4. Increment value, this is optional as it is in RPG. If it is not given 1 is assumed

The similarities between the two end when I start replacing the values in the command. With the RPG operation code the variables just have to numeric. In the command the variables need to either be integer or unsigned integer variables.

01 PGM 02 DCL VAR(&COUNT) TYPE(*INT) 03 DCL VAR(&TO_VALUE) TYPE(*UINT) VALUE(10) 04 DOFOR VAR(&COUNT) FROM(1) TO(&TO_VALUE) 05 ENDDO 06 ENDPGM

Lines 2 and 3: I am sure it did not take you long to determine that &COUNT is an integer and &TO_VALUE is an unsigned integer. I deliberately not given the size of the two variables to show that the CL compiler will give them default sizes.

Declared Variables Name Defined Type Length &COUNT 300 *INT 4 &TO_VALUE 400 *UINT 4

Of course I can give those variables lengths when I define them:

01 PGM 02 DCL VAR(&COUNT) TYPE(*INT) LEN(4) 03 DCL VAR(&TO_VALUE) TYPE(*UINT) LEN(4) VALUE(10) 04 DOFOR VAR(&COUNT) FROM(1) TO(&TO_VALUE)

What happens if you define the To parameter as a decimal (*DEC) variable?

01 PGM 02 DCL VAR(&COUNT) TYPE(*INT) LEN(4) 03 DCL VAR(&TO_VALUE) TYPE(*DEC) LEN(4 0) VALUE(10) 04 DOFOR VAR(&COUNT) FROM(1) TO(&TO_VALUE)

When I compile the program I get an error:

400- DOFOR VAR(&COUNT) FROM(1) TO(&TO_VALUE) * CPD089C 30 Variable &TO_VALUE for parameter TO must be declared TYPE(*INT) or TYPE(*UINT).

Fortunately I can overcome this using the Integer built in function, %INT:

01 PGM 02 DCL VAR(&COUNT) TYPE(*INT) LEN(4) 03 DCL VAR(&TO_VALUE) TYPE(*DEC) LEN(4 0) VALUE(10) 04 DOFOR VAR(&COUNT) FROM(1) TO(%INT(&TO_VALUE))

Line 4: In the To parameter I am using the %INT to convert the value in decimal &TO_VALUE to an integer value.

Let me give an example of the DOFOR in action. This example is based upon a question I was asked by a colleague. He needed to break up a large file, I am calling TESTFILE, into smaller files as the program he had to call can only handle files of up to 9,999 records. Rather than using the Copy File command, CPYF, he asked if it was possible to do the same in SQL. The answer is "Yes".

01 PGM 02 DCL VAR(&DIVISOR) TYPE(*DEC) LEN(5 0) VALUE(9990) 03 DCL VAR(&COUNT) TYPE(*INT) 04 DCL VAR(&OFFSET) TYPE(*DEC) LEN(10 0) VALUE(0) 05 DCL VAR(&RECORDS) TYPE(*DEC) LEN(10 0) VALUE(0) 06 DCL VAR(&TIMES) TYPE(*INT) 07 RTVMBRD FILE(TESTFILE) NBRCURRCD(&RECORDS) 08 CHGVAR VAR(&TIMES) VALUE(&RECORDS / &DIVISOR) 09 IF COND((&TIMES * &DIVISOR) < &RECORDS) + THEN(CHGVAR VAR(&TIMES) VALUE(&TIMES + 1)) 10 DLTF FILE(QTEMP/TABLE*) 11 MONMSG MSGID(CPF0000) 12 DOFOR VAR(&COUNT) FROM(1) TO(&TIMES) 13 RUNSQL SQL('CREATE TABLE QTEMP.TABLE' || %CHAR(&COUNT) + 14 |< ' AS (SELECT * FROM TESTFILE + 15 OFFSET ' || %CHAR(&OFFSET) |< ' ROWS + 16 FETCH FIRST ' || %CHAR(&DIVISOR) |< + 17 ' ROWS ONLY) WITH DATA') + 18 COMMIT(*NC) 19 CHGVAR VAR(&OFFSET) VALUE(&OFFSET + &DIVISOR) 20 ENDDO 21 ENDPGM

Line 2: This variable contains the maximum number of records to be copied into the "child" files.

Lines 3 and 6: My integer variables I will be using in the DOFOR command.

Line 7: Retrieve the number of records in TESTFILE.

Line 8: Divide the number of records retrieved from TESTFILE by the maximum number of records wanted in each "child" file.
    100,000 / 9,990 = 10.01
As &TIMES is an integer the result will be just 10.

Line 9: This is where I determine if I need to perform the For group an 11th time. If the value in &TIMES is multiplied by the maximum number of records wanted, from &DIVISOR, is less than the number of records in the file I need to perform the For group one more time.
    10 * 9,990 = 99,900
As this is less than the number of records in the file I increment the value in &TIMES.

Line 10: Delete all the "child" files, if they already exist.

Lines 12 – 20: The For group will be performed 11 times as &TIMES contains 11.

Lines 13 – 18: This may look confusing but it is not. All that is happening here is the building of the SQL statement to be executed by the RUNSQL command.

The first time the For group is performed the SQL statement looks like:

CREATE TABLE QTEMP.TABLE1 AS (SELECT * FROM TESTFILE OFFSET 0 ROWS FETCH FIRST 9990 ROWS ONLY) WITH DATA

By using the %CHAR built in function on line 13 I convert the number 1 to the character value "1", which can be made part of the file name: TABLE1

This statement creates a new table, TABLE1, based on the SQL statement that follows after the AS.

The offset value looks strange as how can zero be valid? When the data is offset it offset that number of rows, therefore, if I offset zero rows I am at the start of the file.

The FETCH FIRST instructs the statement how many records to copy starting at the offset record.

The WITH DATA is needed as I want a table that contains data.

When the For group loops and this is performed a second time the SQL statement will be:

CREATE TABLE QTEMP.TABLE2 AS (SELECT * FROM TESTFILE OFFSET 9990 ROWS FETCH FIRST 9990 ROWS ONLY) WITH DATA

The table name has been incremented. The offset of 9,990 will mean that the data will be copied starting with the 9,991st record.

In the 11th loop of the For group there are just a hundred records that need to be copied. Even though this is less than 9,900 rows I it does not error. It just copies those last records into TABLE11.

I hope you will find this as useful in CL as it is using the FOR operation code in RPG.

 

You can learn more about the DORFOR command from the IBM website here.

 

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