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)
midrange.com
