Re: embedded SELECT problem

It is simply improper syntax
you'll need to use
update <table> set <x> = (select nvl(<somecolumn>, 0) from <other table>) where you need to make sure your subquery can return only one row. As that subquery will be executed for every row in <table> and you are retrieving *1* record, you could better
select nvl(<somecolumn>,0)
into <somevar>
from <othertable>;

update <table> set <x> = <somevar>

Where of course this code needs to be in your function.

Hth,

Sybrand Bakker, Oracle DBA

"Oleg" <oleg_at_itv.net> wrote in message news:39D50265.F0FBC40A_at_itv.net...
> Hello, All
>
> I'm trying to create function that updates field "x" of table "t" and
> set it to the value selected from another table replacing NULL by 0.
>
> create or replace procedure f(i in int) is
> begin
> update t set x=NVL((select 1 from dual),0);
> end;
>
> This script returns compile time error: "PLS-00103: Encountered the
> symbol "SELECT" when expecting one of the following: ( - + mod not null
> others <an identifier>.".
>
> "update" by itself runs well and returns "0 rows updated", but I'd like
> to have function.
>
> Why it does not work and how can I solve this problem in single
> "update"?
>
> Thanks.
>
>
>
Received on Sat Sep 30 2000 - 06:59:15 CDT