Re: joining 3 files

[jerry ven <jerryven95@xxxxxxxxx>]

when i ran this view file3 it showed  matching records of  FLD1 of file 2 (
which is of 9 length and  character data type and where that 6 digit
number( consecutive 6 digits) is present at random location ) like these
records for example in my data  i got it like 111111xy ,i mean for some of
the fields it's showing correctly up to exactly matching length of 6 but
for some of the fields i am getting two extra characters like 111111xy.
So how to get that exact 6 digit number from file2 for FLD1 which should
match FLD1 of file1 here so that in result of Select * from QTEMP/FILE3 i
should not see those extra characters in  FLD1 of file2 here.


Thanks

On Thu, 23 Jun 2022 at 02:13, Stephen Landess <steve_landess@xxxxxxxxxxx>
wrote:

>    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
>
>
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com