Re: Joining of 2 files

[jerry ven <jerryven95@xxxxxxxxx>]

Even after changing like below I get the same result :-

CREATE VIEW mylib/FILE3 ( FLD1 , fld2 ) AS ( SELECT A.FLD1 , B.FLD2  FROM
LIBa/FILE1 a JOIN LIBb/FILE2 b ON
A.FLD1 = INT(NULLIF(REGEXP_REPLACE(B.FLD2, '[^0-9]',''),'')))



  when I do : select * from mylib/file3.

it shows like previous data set only:-


Fld1                 Fld2

     1                     001              F
     1                     001              V
    23                    ABCDE0023
      1                    ABCDEF1
1234                    AB001234



Thanks








On Fri, 24 Jun 2022 at 23:23, Rob Berendt <rob@xxxxxxxxx> wrote:

> You have something FUBAR in your statement.  For example the following
> immediately pops out as an error:
> SELECT A.FLD1 , B.FLD2)
>
> This ran fine and returned three rows:
> create table qtemp.b  (
> fld2 char(9),
> fldb char(1)
> );
>
> insert into qtemp.b values('000002044', 'A');
> insert into qtemp.b values('00   2044', 'B');
> insert into qtemp.b values('2044ABCDE', 'C');
>
> select b.fld2, INT(NULLIF(REGEXP_REPLACE(B.FLD2, '[^0-9]',''),''))
> from qtemp.b;
>
> create table qtemp.a (
> fld1 dec(6,0),
> fldx char(10));
>
> insert into qtemp.a values(2044, 'HI');
>
> CREATE VIEW qtemp.FILE3 ( FLD1 , fld2 ) AS ( SELECT A.FLD1 , B.FLD2 FROM
> qtemp.a a JOIN qtemp.b b ON
> A.FLD1 = INT(NULLIF(REGEXP_REPLACE(B.FLD2, '[^0-9]',''),'')));
>
> select * from qtemp.file3;
>
> Rob Berendt
> --
> IBM Certified System Administrator - IBM i 6.1
> Group Dekko
> Dept 1600
> Mail to:  7310 Innovation Blvd, Suite 104
>           Ft. Wayne, IN 46818
> Ship to:  7310 Innovation Blvd, Dock 9C
>           Ft. Wayne, IN 46818
> http://www.dekko.com
>
> -----Original Message-----
> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
> jerry ven
> Sent: Friday, June 24, 2022 12:39 PM
> To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
> Subject: Joining of 2 files
>
> CAUTION: This email originated from outside of the organization. Do not
> click links or open attachments unless you recognize the sender and know
> the content is safe.
>
>
> Hi,
>
> How to join two files like :-
>
> FLD1 of file1 is of 6 (Decimal) and FLD2 of file2 of 9 character and we are
> looking a match on these fields from file1's field1 ( it has some 6 digit
> numbers) to file2' field2 ( which too have these 6 digit number but it's
> position keep on varying in this field ( not fixed position but could be
> between 1st to 4th position as total length of this number is 6 and field's
> length is 9 here)
>
> so i tried  below join but it's giving results like below:-
>
> CREATE VIEW mylib/FILE3 ( FLD1 , fld2 ) AS ( SELECT A.FLD1 , B.FLD2) FROM
> LIBa/FILE1 a JOIN LIBb/FILE2 b ON
> A.FLD1 = INT(NULLIF(REGEXP_REPLACE(B.FLD2, '[^0-9]',''),'')))
>
> fld1 fld2
> 1 ABCDEF1
> 2,567 AB002567
> 1 0001       F
> 21 ABCDE021
> File1 ( have field1 let's say it's name is X - Data type Decimal length 6,
> it has other fields as well but we are currently not bothered about those
> fields)
>
> file2( have fiield1 let's say it's name is Y- Data type is Character with 9
> length - it too have other fields in this file but currently we are not
> bothered about those fields)
>
>
> Just for example some dummy Data in File1's field X:-
>
> 1) 2044
> 2) 32,018
> 3)106
>
>
>
> Just for example some dummy Data in File2's field Y:-
>
> *
> *B
> *END
> 001 F
> 001 V
> *
> *
> ZC057882
> ZD071304
> ZW002711
> ABCDE
>
>
> So the requirement is to join these two fields ( of these two files) in
> such a way that for that the number( whose max length is 6 and data type is
> decimal) in this field 1  we have to find it's corresponding match in Field
> 2 ( of file2) provided when we search a match in Field 2 ( of file2) then
> we should extract only the equivalent number part from file2's field 2.
>
> So If I get below kind of records :-
>
> field 1                                                            field 2
>
> 2044                                                          000002044
> ----it's fine.
> 2044                                                         00space2044
> ---it's not fine
> 2044                                                         2044ABCDE
> ---it's not fine
>
>
>
>
> Thanks
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-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
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-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