WallaceC
Beginner
Joined: 17 Dec 2002
Posts: 22
Topics: 10
|
 Posted: Mon Dec 30, 2002 2:11 pm Post subject: Joiining 4 tables using Left Outer Join |
 |
|
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? |
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
