Re: joining of two files

[Peter Dow <petercdow@xxxxxxxxx>]

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