Re: joining of two files

No, it would not be equivalent.

A left join always includes all records from the "primary" file, in this case file1.  If there is no matching record in the"secondary" file, file2, then any fields from file2 are null in the result record.

Regarding your other concern, I'm assuming the data is something like this:

File1.Field1 File2.FIeld2        Match?
(numeric 6.0)    (character 9)
    123          000123                Yes
    123          *00123                No
    123 ABC123                No
    123          ABC000123             No
    123          000123***             No

I'm no expert on regular expressions, but it seems lik your proposed query would result in

File1.Field1     File2.FIeld2        Match?
(numeric 6.0)    (character 9)
    123          000123                Yes
    123          *00123                Yes
    123 ABC123                Yes
    123          ABC000123      No
    123          000123***             Yes

because it appears to remove any non-numeric characters from the first 6 positions of file2.field2, then converts the result to an integer.

Which I thought is not exactly what you want, because you do not want 000123*** to match 123.


On 6/29/2022 1:53 PM, jerry ven wrote:

Hi,


this seems to work but would it be equivalent to :-

SELECT *
FROM file1 a
left JOIN file2 b
ON digits(a.field1) = trim(b.field2)


because primarily i was looking for a matching value from field1 into the
field2 (so i believe i should use left join here like above SQL Query ?)
secondly this field2 could have some non numeric values like '*' in the
beginning position of this field2( from which we are just concerned for
first 6 positions only for comparing the field1 values from file1)
so as i said we have to ignore such records in the matched output( because
leading '*' values in field2 are irrelevant here - we just need a match
from number ( field1) to number( field2 first 6 positions)) so if i use
this sql query :-

select a.field1, b.field2 from lib1/file1 a left join lib2/file2 b on
a.field1 = INT(NULLIF(REGEXP_REPLACE(SUBSTR(B.FIELD2,1,6),
'[^0-9]',''),''))



Then considering leading star ('*') values in the beginning of field2
values, which query would better suit this join here?



Thanks.







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

Ok, I misunderstood the part about

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

How about

SELECT *
FROM file1
JOIN file2
ON digits(file1.field1) = trim(file2.field2)


On 6/29/2022 12:34 PM, jerry ven wrote:

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 messageemail:MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:https://lists.midrange.com/mailman/listinfo/midrange-l
oremail:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
athttps://archive.midrange.com/midrange-l.

Pleasecontactsupport@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
athttps://archive.midrange.com/midrange-l.

Please contactsupport@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:https://amazon.midrange.com