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