Re: Outer join problem
Date: Fri, 18 Jun 2004 22:00:45 -0400
Message-ID: <40d39ef8$1_6_at_corp.newsgroups.com>
Neeraj,
It would be interesting to have a more complete functional description of these tables / fields and the functional intent of the query.
Having said that, you'll never get the 3rd row in the result with your query since that would require CHARGE.CODE to be null and also CHARGE.CODE=CODE.CODE to be true. Since that expression is never true, that's the basic reason for the behavior you are seeing.
You'll similarly have the same problem with comparisons of the other columns of table "charge".
Try this (just as a test) or use the non-standard (+) syntax if you wish:
SELECT payer.period, payer.cust, payer.ba, charge.ba,
payer.ctrl_no, charge.code, code.cd_typ, charge.receiver FROM payer LEFT JOIN charge
ON charge.period = payer.period AND charge.cust = payer.cust AND charge.ba = payer.ba AND charge.ctrl_no = payer.ctrl_no, outcharge, code, (SELECT * FROM control1 UNION SELECT * FROM control2) tmp_control;
Regards... Jon
Jon Armstrong
<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.
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World! -----== Over 100,000 Newsgroups - 19 Different Servers! =----- Received on Sat Jun 19 2004 - 04:00:45 CEST