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



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
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.