IBMi (AS400) fans only ' My files for all my examples:

#IBMiSample


Run this SQLRPGLE to create and populate the files to use my further examples:

**free

//*****************************

//* create CLANA00F & OTORD00F*

//*****************************

ctl-opt option(*nodebugio) dftactgrp(*no) actgrp(*new);

// The EXEC SQL is never executed. It is used at compile time.

exec sql Set Option Commit = *None;

// Create File

exec sql

CREATE or REPLACE TABLE CLANA00F (

CLANN0 CHARACTER(01) NOT NULL WITH DEFAULT,

CLCCL0 NUMERIC(7, 0) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

CLCIB0 CHARACTER(03) NOT NULL WITH DEFAULT,

CLNOM0 CHARACTER(50) NOT NULL WITH DEFAULT

)

RCDFMT CLANA

;

// Create index

exec sql

CREATE INDEX CLANA01L ON CLANA00F (CLCCL0 ASC);

// Fill values

exec sql

INSERT INTO CLANA00F (CLCIB0, CLNOM0)

VALUES ('EN ','Rod Stewart');

exec sql

INSERT INTO CLANA00F (CLCIB0, CLNOM0)

VALUES ('EN ','Bobby McFerrin');

exec sql

INSERT INTO CLANA00F (CLCIB0, CLNOM0)

VALUES ('IT ','Adriano Celentano');

exec sql

INSERT INTO CLANA00F (CLCIB0, CLNOM0)

VALUES ('IT ','Vasco Rossi');

// Create file

exec sql

CREATE or REPLACE TABLE OTORD00F (

OTANN0 CHARACTER(01) NOT NULL WITH DEFAULT,

OTCOR0 NUMERIC(7, 0) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

OTTIP0 CHARACTER(02) NOT NULL WITH DEFAULT,

OTCCL0 NUMERIC(7, 0) NOT NULL WITH DEFAULT,

OTDTA0 NUMERIC(6, 0) NOT NULL WITH DEFAULT,

OTDTP0 NUMERIC(6, 0) NOT NULL WITH DEFAULT,

OTCCM0 CHARACTER(06) NOT NULL WITH DEFAULT

)

RCDFMT OTORD

;

// Fill values

exec sql

INSERT INTO OTORD00F (OTTIP0,OTCCL0,OTDTA0,OTDTP0,OTCCM0)

VALUES ( '10', 1 , 190108 , 190114 , '101 ' );

exec sql

INSERT INTO OTORD00F (OTTIP0,OTCCL0,OTDTA0,OTDTP0,OTCCM0)

VALUES ( '10', 2 , 190108 , 190112 , '103 ');

exec sql

INSERT INTO OTORD00F (OTTIP0,OTCCL0,OTDTA0,OTDTP0,OTCCM0)

VALUES ( '10', 3 , 190112 , 190116 , '102 ');

exec sql

INSERT INTO OTORD00F (OTTIP0,OTCCL0,OTDTA0,OTDTP0,OTCCM0)

VALUES ( '10', 4 , 190108 , 190121 , '102 ');

*inlr = *on;

Don't forget to set the screen to 132 columns!





I appreciate all the comments made on this blog.