Advanced Update Query Question

From: Dave Hartman <dhartman_at_enter.net>
Date: 1998/01/31
Message-ID: <6avfr8$n45@news.enter.net>#1/1

I am developing an Update Query in Oracle 7.1.6.2. I have an Update that is using a subquery to return the values. This subquery is outer joining to the table I am updating. But I know that one of the columns returned in the subquery will be null, so I used the NVL(Col1, 0) option. But this doesn't work it still returns a NULL for this column. When I execute the subquery on its own (adding in the join to the table i am updating it works fine and returns 0 when a column is null. So I decided to join to the table I am updating again inside the subquery and it works fine. I can't really come up with an explanation for this. Anybody else see this before. Here is what I mean:
ACTUAL

---

year CHAR(4) PK NOT NULL
Customer CHAR(1) PK NOT NULL
Volume NUMBER
BudVolume NUMBER

BUDGET

---

year CHAR(4) PK NOT NULL
Customer CHAR(1) PK NOT NULL
BudVolume NUMBER

select * from actual
year customer volume BudVolume

1993    1         100   <NULL>
1994    1         50    <NULL>
1995    1         150   <NULL>

select * from budget
year customer Budvolume

1994    1         100
1995    1         100

HERE IS THE UPDATE update actual a
set a.budvolume =
(

select nvl(b.Budvolume,0)
from budget b
where a.customer = b.customer(+)
  and a.year = b.year(+)
)
/

3 rows updated

expected results ->

select * from actual
year customer volume BudVolume

1993    1         100   0
1994    1         50    100
1995    1         150   100

but instead the first row has a NULL for BudVolume

Unless I do this

update actual a
set a.budvolume =
(

select nvl(b.Budvolume,0)
from budget b,

     actual c

where c.customer = b.customer(+)
  and c.year = b.year(+)
  and a.year = c.year
  and a.customer = c.customer

)

this of course doesn't seem efficient...Any questions comments...Thanks

Dave Hartman Received on Sat Jan 31 1998 - 00:00:00 CST