Re: Advanced Update Query Question

Dave Hartman wrote:
>
> 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:
>
>....
>
> 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(+)
> )
> /
>
> ...
>
> Dave Hartman

Hi Dave,

I get the same thing as your first example: I expect it's because the subquery returns NO record(s), so the NVL() doesn't return any value. Your second example does an outer join between the two tables and does return a row, and the NVL() succeeds. I found that I can use a MAX() function using your first example and get the result I want, without having to do the extra table join as in second example. eg:

  update actual a
  set a.budvolume =
    (
    select nvl(max(b.Budvolume),0)
    from budget b
    where a.customer = b.customer(+)
      and a.year = b.year(+)
    );

Using MAX or any (all?) of the other group functions will return a row. eg:

  select count(*) from table;

will still return a row with a count of 0 even if table is empty. So the nvl(max(b.Budvolume),0) will cause the max(b.Budvolume) to return a null in the subquery even if no rows match, and can be tested by the nvl() function.

Hope this helps....

-- 
Barry P. Grove                          BarrySoft Systems Applications
grovebg_at_iSTAR.ca                        (604)929-5433
Developer, Oracle DBA, Unix Sysadmin    North Vancouver, BC, V7H-2G4