Re: Joining of 2 files

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