RE: inner join SQL

You are joining on t1.positive = t2.positive and t1.negative = t2.negative. Only one row matches that. The other row has differing positive and negative values.

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of A Paul
Sent: Wednesday, September 18, 2013 8:33 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: inner join SQL

Thanks very much for all the responses. It appears I have a strange problem here which I am unable to figure out. I am posting my field definitions and I am taking only fields that are required. Please see attached notepad to see the columns and questions properly. Also pasted below for your reference (if it doesn't not show columns properly below, please refer attached notepad).

MFILE1
=====

PSP ACCT brch TYC yrm positive negative


P ABCPQ 16 AB 201312 0.0000 645.0000 P ABCPQ 16 PQ 201312 1920.0000 0 P ABCPQ 16 RS 201312 0.0000 192.0000 D 234C 16 25 201312 144.0000 270.0000 D 234N 16 25 201312 149.0000 277.0000


CFILE2
=====
PSP ACCT brch TYC yrm positive negative


P ABCPQ 16 AB 201312 0 645
P ABCPQ 16 PQ 201312 1920 0
P ABCPQ 16 RS 201312 0 192
D 234C 16 25 201312 144 270 D 234N 16 25 201312 149 277

CFILE2 fields defined as below :
-------------------------------
Field File Type Length Scale PSP CFILE1 VARCHAR 1 ACCT CFILE1 CHARACTER 5 BRCH CFILE1 CHARACTER 2 TYC CFILE1 CHARACTER 5 YRM CFILE1 CHARACTER 6 POSITIVE CFILE1 NUMERIC 7 NEGATIVE CFILE1 NUMERIC 7
MFILE1 fields defined as below:
-----------------------------
Field File Type Length Scale PSP MFILE2 CHARACTER 1 ACCT MFILE2 CHARACTER 5 BRCH MFILE2 CHARACTER 2 TYC MFILE2 CHARACTER 5

YRM MFILE2 CHARACTER 6 POSITIVE MFILE2 DECIMAL 31 4 NEGATIVE MFILE2 DECIMAL 31 4

SQL:
=====
SELECT * FROM (SELECT PSP,ACCT,BRCH, TYC, YRM,POSITIVE, NEGATIVE FROM MFILE1) T1

INNER JOIN( SELECT PSP,ACCT,BRCH, TYC, YRM,POSITIVE, NEGATIVE FROM CFILE2
) T2 ON T2.PSP = T1.PSP T2.ACCT = T1.ACCT AND T2.BRCH = T1.BRCH AND T2.TYC = T1.TYC AND T2.YRM = T1.YRM AND T2.POSITIVE = T1.POSITIVE AND T2.NEGATIVE = T1.NEGATIVE

My results after executing above SQL are:



PSP ACCT brch TYC yrm positive negative


P ABCPQ 16 AB 201312 0.0000 645.0000 P ABCPQ 16 PQ 201312 1920.0000 0 P ABCPQ 16 RS 201312 0.0000 192.0000 The SQL is not getting me the two records that do match as well, does anyone what I am missing? To make it work am I supposed to change field definitions to match?




________________________________
From: Jack Callahan <jjcllhn@xxxxxxxxx>
To: RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
Sent: Wednesday, 18 September 2013, 15:50
Subject: Re: inner join SQL


Go the distance and use row value expressions for the comparison. Looks
more like a multi-part key to old time RPGers:

SELECT *
FROM file1 t1 JOIN file2 t2
ON ( t1.acct, t1.brch, t1.tyc, t1.yrm ) = (t2.acct, t2.brch, t2.tyc,
t2.yrm) AND (t1.positive=t2.positive OR t1.negative=t2.negative);