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



By the way, you may have a reason why you used the style you did but the
following produces the same result:

select *
from file1 t1 join file2 t2 on t1.acct=t2.acct and t1.brch=t2.brch and
t1.tyc=t2.tyc and t1.yrm=t2.yrm and (t1.positive=t2.positive or
t1.negative=t2.negative);

Hi Paul,

You don't have POSITIVE and NEGATIVE in T2 so you should get an error
"Column not in table T2".

After I placed both in T2 and my change earlier, it worked for me and
produced 5 rows.

Yours truly,

Glenn Gundermann


Hi Paul,

I'm not at my computer but give this a try:

Change:
(T2.POSITIVE = T1.POSITIVE) OR (T2.NEGATIVE = T1.NEGATIVE)

To:
(T2.POSITIVE = T1.POSITIVE OR T2.NEGATIVE = T1.NEGATIVE)

Glenn Gundermann

-----Original Message-----
From: A Paul <amandapaul1@xxxxxxxxxxx>
Sender: rpg400-l-bounces@xxxxxxxxxxxx
Date: Wed, 18 Sep 2013 18:42:26
To: RPG programming on the IBM i \(AS/400 and
iSeries\)<rpg400-l@xxxxxxxxxxxx>
Reply-To: "RPG programming on the IBM i \(AS/400 and iSeries\)"
<rpg400-l@xxxxxxxxxxxx>
Subject: inner join SQL

FILE1
=====       
       
ACCT brch TYC yrm flag QUANTITY positive negative
             
             
ABCPQ 1    21 201312 2       645        0 645
ABCPQ 1    25 201312 1        1920      1920 0
ABCPQ 1    26 201312 2          192      0 192
1234C 1     25 201312 1         144      144  270
 
 
FILE2
=====       
       
ACCT brch TYC yrm flag QUANTITY positive negative
             
             
ABCPQ 1    21 201312 2        645   0       645
ABCPQ 1    25 201312 1       1920  1920       0
ABCPQ 1    26 201312 2        192  0         192
1234C 1    25 201312 1        144  144         0
1234C 1    25 201312 1         144  0         270
 
SELECT * FROM                                                          
(SELECT ACCT,BRCH,TYC,YRM,FLAG FROM FILE2)
T2                             
                              
         INNER JOIN(                                                   
SELECT 
ACCT, BRCH, TYC, YRM,FLAG,POSITIVE,NEGATIVE
FROM FILE1                                                       
) T1                                                                   
ON T2.ACCT = T1.ACCT
AND T2.BRCH = T1.BRCH
AND T2.TYC = T1.TYC
AND T2.YRM = T1.YRM
AND (T2.POSITIVE = T1.POSITIVE) OR (T2.NEGATIVE =
T1.NEGATIVE)                           
 I am expecting to see my outfile from this SQL  have all 5 records from
file2 ( they match with file1 on the conditions above), but  i am
getting
only 4records match.
Can someone tell me what need to be fixed in my SQL to get all 5
records.
I am having troule with record for account 1234C.
For account 1234C I have two records and they both match to 1234C record
in FILE1(if you see T2.positive = t1.POSITIVE AND T2.NEGATIVE =
T1.NEGATIVE)



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.