Re: Outer join problem

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.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  

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 Sat Jun 19 2004 - 08:42:46 CEST