Re: Joining of 2 files -- MIDRANGE-L

Hi,

What if I just need to match fld1( in file1 whose data type is packed
decimal and length is 6) to fld2 ( in file2 whose data type is character
and length is 9) provided that that fld1 would be in the first 6
positions of fld2 only and if it is found in the first 6 positions then
how could we modify below SQL query?

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

Thanks.

On Sun, 26 Jun 2022 at 10:33, jerry ven <jerryven95@xxxxxxxxx> wrote:

Hi,

For more clarification please find the details as followings:-

1) FLD1 length 6 (data type DECIMAL (packed)) FOR FILE1

2) FLD2 length 9 ( Data type CHARACTER) FOR FILE2

3) Sample data :-

Fld1 Fld2
Test Criteria
123456 abc123456
fine
1 000001xyz
fine
123 ******123
not fine
123 000123***
fine
1234 000001234
fine
1234 001234***
fine
1234 01234****
not fine
12 12*******
not fine
12 012******
not fine
12 000012***
fine
1 *****1***
not fine
123456 123456***
fine
123456 **123456*
fine
123456 123123456
fine
123 123456789
not fine
12 121212121
not fine
1 272272131
not fine
123 550000123
fine
12 120000000
not fine
1 100000000
not fine

here * means any value which is non zero and could be any number
,blanks,space,stars(*) any special character or any character (a-z,A-Z))
etc.
So if the test criteria is fine( meets all the conditions for a number
which is present in the FLD1( From file1) to FLD2( From file2) then
only corresponding matching records from Fld1 to Fld2 should be displayed
like below :-

Fld1 Fld2

123456 123456

1 000001

123 000123

1234 001234

1234 001234

12 000012

123456 123456

123456 123456

123456 123456

123 000123

Thanks

On Sat, 25 Jun 2022 at 17:43, jerry ven <jerryven95@xxxxxxxxx> wrote:

This JOINEXPR is showing the wrong results as I was expecting it would
only return the data from field2 if it's just numeric it's in
fact extracting the matching number from field2 here
but it's not what I want.

I just want if the field2 has just that number in it matching with
field1's number ( whose maximum length is 6 and data type is decimal (
packed)) and if there are no blanks or any characters or spaces in it then
I will say it's a match.
I mean space or blanks in FLD1 is fine but spaces or blanks or any other
non numeric number in the FLD2 is not fine example:- 123 to 000123
is fine but 123 to 00 123 is not fine.

current query show results like this:-

Fld1 Fld2 JOINEXPR

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

Also, instead of this JOINEXPR is it possible just to have two
columns (field names like currently it's displaying in existing query
results where I get FLD1,FLD2 Names ) in the query output where FLD2 just
displays matching numeric numbers in it from FLD1 ( with mentioned
criteria as explained above) ?

Thanks

On Sat, 25 Jun 2022 at 14:35, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

If you do not include the join expression in your view columns you will
only
see the content of the original field.

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

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization – Education – Consulting on IBM i

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training
them
and keeping them!"
„Train people well enough so they can leave, treat them well enough so
they
don't want to.“ (Richard Branson)

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
jerry
ven
Sent: Samstag, 25. Juni 2022 10:24
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx

Subject: 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

--
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