Re: Outer join problem

Thanks a lot, Jon and Romeo. Your explainations cleared the fog and I was finally able to see the missing link (CHARGE.CODE = CODE.CODE(+)).

rolympia_at_hotmail.com (Romeo Olympia) wrote in message news:<42fc55dc.0406182242.23de9a2e_at_posting.google.com>...
> What exactly do you want done? We could make your output come out but
> it doesn't necessarily mean that we're meeting your logical
> requirements. Some more explanation would be helpful.
>
> Also, if you are in 9i at least, consider using ANSI OUTER JOIN
> syntax. Some restrictions using the (+) operator may be cirvumvented
> by this.
>
> But to get you started. Written in the traditional Oracle outer join
> syntax. Again (not sure if this meets your "business" need).
>
> SELECT PAYER.PERIOD, PAYER.CUST, PAYER.BA, PAYER.CTRL_NO,
> CHARGE.CODE, CHARGE.CD_TYP, CHARGE.RECEIVER
> FROM PAYER,
> (select CHARGE.*, CD_TYP
> from CHARGE, OUTCHARGE, CODE
> where OUTCHARGE.PERIOD=CHARGE.PERIOD
> AND OUTCHARGE.CUST=CHARGE.CUST
> AND OUTCHARGE.SEQ1=CHARGE.SEQ1
> AND OUTCHARGE.SEQ2=CHARGE.SEQ2
> and CHARGE.CODE = CODE.CODE(+)) CHARGE,
> (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
>
> PERIOD CUST BA CTRL_NO CODE CD_TYP
> RECEIVER
> ---------- ---------- ---------- ---------- ---------- ----------
> ----------
> 1 1 1 10 20 21
> 99
> 1 1 2 10 20 21
> 90
> 1 1 3 10
>
>
>
>
> jain-neeraj_at_lycos.com wrote in message news:<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));
> > insert into PAYER values (1,1,1,10);
> > insert into PAYER values (1,1,2,10);
> > insert into PAYER values (1,1,3,10);
> >
> > 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));
> > insert into CHARGE values (1,1,1,10,4,5,20,99);
> > insert into CHARGE values (1,1,2,10,4,6,20,90);
> >
> > create table OUTCHARGE (PERIOD number(2), CUST number(2), SEQ1
> > number(2), SEQ2 number(2));
> > insert into OUTCHARGE values (1,1,4,5);
> > insert into OUTCHARGE values (1,1,4,6);
> >
> > QUERY is:
> >
> > SELECT PAYER.PERIOD, PAYER.CUST, PAYER.BA, PAYER.CTRL_NO,
> > CHARGE.CODE, CODE.CD_TYP, CHARGE.RECEIVER
> > 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;
> >
> > Output should be (3 rows):
> > (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 Mon Jun 21 2004 - 09:57:02 CEST