Outer join problem

[jain-neeraj]

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.

[Jon Armstrong]

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

<ja*********@lycos.com> wrote in message
news:30**************************@posting.google.c om...

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! =-----

[Romeo Olympia]

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


ja*********@lycos.com wrote in message news:<30**************************@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.

[jain-neeraj]

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(+)).

ro******@hotmail.com (Romeo Olympia) wrote in message news:<42**************************@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


ja*********@lycos.com wrote in message news:<30**************************@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.