MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Joiining 4 tables using Left Outer Join

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
WallaceC
Beginner


Joined: 17 Dec 2002
Posts: 22
Topics: 10

PostPosted: Mon Dec 30, 2002 2:11 pm    Post subject: Joiining 4 tables using Left Outer Join Reply with quote

I would like to join 4 tables to get counts for each country/state:

TBL1 (KEY=KEY1,CTRY_CODE,STATE_CODE)
Code:

KEY1 CTRY_CODE STATE_CODE
---  --------- ----------
01   US        CA
01   US        OR
01   CA        ON
01   CA        BC

TBL2 (KEY=CTRY_CODE)
Code:

CTRY_CODE CTRY_DESC
--------- -------------
US        UNITED STATES
CA        CANADA

TBL3 (KEY=STATE_CODE)
Code:

STATE_CODE STATE_DESC
---------- ----------------
CA         CALIFORNIA
OR         OREGON
ON         ONTARIO
BC         BRITISH COLUMBIA


TBL4 (KEY=KEY1,KEY2)
Code:

KEY1 KEY2 CTRY_DESC STATE_DESC DT
---- ---- --------- ---------- ----------
01   A    US        CA         2002-12-01
01   B    US        CA         2002-12-01
01   C    US        OR         2002-12-08
01   D    CN        ON         2002-12-15
01   E    CN        ON         2002-12-15


EXPECTED RESULTS
Code:

CTRY_DESC      STATE_DESC       COUNT DT
---------      ---------------- ----- ---------- 
CANANA         BRITISH COLUMBIA ----- ----------
CANADA         ONTARIO          2     2002-12-15
UNITED STATES  CALIFORNIA       2     2002-12-01
UNITED STATES  OREGON           1     2002-12-08


This is the cursor I was using:
Code:

EXEC SQL
  DECLARE  TMP_CSR CURSOR WITH RETURN FOR
   SELECT  B.CTRY_DESC
          ,C.STATE_DESC           
          ,COUNT(*)
          ,D.DT                             
     FROM  TBL1 A
          ,TBL2 B         
          ,TBL3 C       
           LEFT OUTER TBL4 D
                ON A.KEY1       = D.KEY1
               AND A.CTRY_CODE  = D.CTRY_CODE
               AND A.STATE_CODE = D.STATE_CODE   
    WHERE A.KEY1 = 01
      AND A.CTRY_CODE  = D.STATE_CODE
      AND A.STATE_CODE = D.STATE_CODE
      AND A.CTRY_CODE  = B.CTRY_CODE   
      AND A.STATE_CODE = C.STATE_CODE
   GROUP BY  B.CTRY_DESC
            ,C.STATE_DESC
            ,D.DT
   ORDER BY  B.CTRY_DESC
            ,C.STATE_DESC
            ,D.DT
   WITH UR
END-EXEC
                                                 

However, I got an SQLCODE-338 when I compiled the code. I would appreciate if anyone could tell what's wrong with my cursor?
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Mon Dec 30, 2002 2:28 pm    Post subject: Reply with quote

Wallace,

An sql code -338 means you have an Invalid ON CLAUSE. try changing your On clause to the following.I added the parenthesis and changed the conditions on clause.


Code:

 ON  (     D.KEY1       = A.KEY1
      AND D.CTRY_CODE  = A.CTRY_CODE
      AND D.STATE_CODE = A.STATE_CODE)   


Hope this helps...

cheers

kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group