RE: joining 3 files

DROP TABLE QTEMP/FILE1;
DROP TABLE QTEMP/FILE2;
DROP VIEW QTEMP/FILE3;

CREATE TABLE QTEMP/FILE1
( FLD1 NUMERIC (6 , 0) NOT NULL WITH DEFAULT
, FLD2 CHAR (20) NOT NULL WITH DEFAULT
)

Insert into QTEMP/FILE1
VALUES
( 111111 , 'File 1 rec 1' )
, ( 222222 , 'File 1 rec 2' )
, ( 333333 , 'File 1 rec 3' )
, ( 444444 , 'File 1 rec 4' )


CREATE TABLE QTEMP/FILE2
( FLD1 CHAR (09) NOT NULL WITH DEFAULT
, FLD2 CHAR (20) NOT NULL WITH DEFAULT
)

Insert into QTEMP/FILE2
VALUES
( '111111 ' , 'File 2 rec 1' )
, ( ' 222222 ' , 'File 2 rec 2' )
, ( ' 333333 ' , 'File 2 rec 3' )
, ( ' 444444' , 'File 2 rec 4' )
, ( ' 111111 ' , 'File 2 rec 5' )
, ( ' 222222 ' , 'File 2 rec 6' )
;

CREATE VIEW QTEMP/FILE3
( FLD1 , FLD2 , FLD3 , FLD4 ) AS
( SELECT A.FLD1 , A.FLD2 , B.FLD1 , B.FLD2
FROM QTEMP/FILE1 a
JOIN QTEMP/FILE2 b
ON DIGITS(A.FLD1) = CAST(TRIM(B.FLD1) AS CHAR(6))
)

Select * from QTEMP/FILE3