Re: Problem with outer join

From: David Scott <dscott_at_is.net>
Date: 1996/01/16
Message-ID: <dscott-1601962345420001_at_dscott.is.net>#1/1

In article <racetrk1.4.AA3AD980_at_mindspring.com>, racetrk1_at_mindspring.com (RaceTrac Petroleum) wrote:

> These are the table that we working with:
>
> Table A:
> key_id Date Value
> 1 04-DEC-95 1000
>
> Table B:
> key_id
> 1
> 2
>
> This SQL statement generate the results posted below:
> This is the result that we want.
>
> select key_id, nvl(sum(value),0)
> from A, B
> where A.key_id = b.key_Id (+)
>
> Results:
> key_id sum(Value)
> 1 1000
> 2 0
>
> The same SQL stmt with the date criteria returns only 1 row.
>
> select key_id, nvl(sum(value),0)
> from A, B
> where A.key_id = b.key_Id (+)
> and date = '04-DEC-95'
>
> Results:
> key_id sum(Value)
> 1 1000
>
> Does any one know a way around this?

The basic difficulty here, even with the outer join, is that the second TABLE_B record has no date value. You could try (I do not warrant these solutions, my databases are at work - I'm at home) two possibilities: === 1 ===
  select key_id, nvl(sum(value),0)
  from A, B
  where A.key_id = b.key_Id (+)
  and date = '04-DEC-95'
  or date is NULL
=== 2 ===
  insert into TABLE_A (key_id, Date, Value)   select
  keyid, '04-DEC-95', 0
  from TABLE_B where keyid not in
  (select keyid from TABLE_A)

or something like this. Let me know if these work, or if I should study further on your behalf. Hope this helps!

---

David Scott
...how can I keep from singing?
email: dscott_at_is.net

---

Received on Tue Jan 16 1996 - 00:00:00 CET