Outer join problem
Date: 18 Jun 2004 09:35:59 -0700
Message-ID: <30b5aacb.0406180835.55c88998_at_posting.google.com>
Hi,
We have a problem in our mobile calls billing software. To solve it, I
need an outer join in a complicated query. Following are the
simplified tables with sample data:
create table CONTROL1 (CTRL_NO number(2));
insert into CONTROL1 values (10);
create table CONTROL2 (CTRL_NO number(2));
-- does not have any data
create table CODE (CODE number(2), CD_TYP number(2));
insert into CODE values (20, 21);
create table PAYER (PERIOD number(2), CUST number(2), BA number(2),
CTRL_NO number(2));
create table CHARGE (PERIOD number(2), CUST number(2), BA number(2),
CTRL_NO number(2), SEQ1 number(2), SEQ2 number(2), CODE number(2),
RECEIVER number(2));
create table OUTCHARGE (PERIOD number(2), CUST number(2), SEQ1
number(2), SEQ2 number(2));
QUERY is:
SELECT PAYER.PERIOD, PAYER.CUST, PAYER.BA, PAYER.CTRL_NO,
CHARGE.CODE, CODE.CD_TYP, CHARGE.RECEIVER
Output should be (3 rows):
insert into PAYER values (1,1,1,10);
insert into PAYER values (1,1,2,10);
insert into PAYER values (1,1,3,10);
insert into CHARGE values (1,1,1,10,4,5,20,99);
insert into CHARGE values (1,1,2,10,4,6,20,90);
insert into OUTCHARGE values (1,1,4,5);
insert into OUTCHARGE values (1,1,4,6);
FROM PAYER, CHARGE, OUTCHARGE, CODE,
(select * from CONTROL1
union select * from CONTROL2) TMP_CONTROL
WHERE PAYER.CTRL_NO=TMP_CONTROL.CTRL_NO
AND CHARGE.PERIOD(+)=PAYER.PERIOD
AND CHARGE.CUST(+)=PAYER.CUST
AND CHARGE.BA(+)=PAYER.BA
AND CHARGE.CTRL_NO(+)=PAYER.CTRL_NO
AND CHARGE.CODE=CODE.CODE
AND OUTCHARGE.PERIOD=CHARGE.PERIOD
AND OUTCHARGE.CUST=CHARGE.CUST
AND OUTCHARGE.SEQ1=CHARGE.SEQ1
AND OUTCHARGE.SEQ2=CHARGE.SEQ2;
(1,1,1,10,20,21,99)
(1,1,2,10,20,21,90)
(1,1,3,10,null,null,null)
When I use the above query, I get only the first 2 rows, not the third. What mistake am I doing?
Thanks in advance. Received on Fri Jun 18 2004 - 18:35:59 CEST