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);
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?