Compile and Run this SQLRPGLE to create table CLANA00F, index CLANA01L and table OTORD00F, and populate:
**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 myLib/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 myLib/CLANA01L ON myLib/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 myLib/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;
https://www.linkedin.com/in/aldosucci/
