× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.