RE: joining of two files

Didn't we tell you how you can compare/convert those values?
For the join you have to use exactly the same expression.

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: Mittwoch, 29. Juni 2022 21:35
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: joining of two files

Hi,

This is giving me wrong join like below: along with some good join -

field1 field2
testing of join
123 000123
good
1234 001234
good
1234 001234AB wrong(
because of these extra AB)
4567 004567
GOOD
4567 004567AB
Wrong because of this extra AB
105 000105
good


so how to get ridoff these extratwo characters coming at end of field2(like
above here 'AB') if anyhow we are able to get ridoff this extra characters
then i think all seem good join here.



thanks..



On Thu, 30 Jun 2022 at 00:47, Peter Dow <petercdow@xxxxxxxxx> wrote:

Hi Jerry,

SELECT *
FROM file1
JOIN file2
ON digits(file1.field1) = substr(file2.field2,1,6)

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx
pdow@xxxxxxxxxxxxxx


/
On 6/29/2022 10:38 AM, jerry ven wrote:

Hi,

there won't be any trailing or leading blanks or any leading/trailing
characters :-

If there is any non numeric value in the first 6 positions in the
Field2

of

file2 then that record would not be considered.

A good join example would be like below :-


File1/Field1 File2/Field2

293 000293



Thanks

On Wed, 29 Jun 2022 at 21:04, jerry ven<jerryven95@xxxxxxxxx> wrote:

Hi,


I have two files lets say file1 and file2.

file1 has multiple fields but let's say currently we are just
focused

for

it's field1 and similarly file2 have multiple such fields and we
are

just

concerned with field2.

field1 of file1 is of packed decimal with 6 length.

field2 of file2 is of character data type and of length 9.

Now we want to join file1's field1 to the fiel2's field2 in such a
way that we are sure that field2 (would have that matching value
from

file1's

field1 in it's( inside field2) first 6 positions only.
and even if it's found in field2 beyond 6th position then we are
not concerned with that match.

So how to join these files to get the matching records from
fiile1's
field1 to file2's field2.

I mean what should be the join SQL query to get these matching records?



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