This sql works on the sql window but when i want to install it in a procedure, i've got a stupid message error saying that after NVL i can not get a select.
update weather_data_buffer wdb set wdb.effect_value= nvl((select wdc.effect_value from weather_data_current wdc
where wdb.wstation_code=wdc.WSTATION_CODE and wdb.effect_code=wdc.EFFECT_CODE and wdb.date_time=wdc.DATE_TIME)
, (select wdf.effect_value from weather_data_forecast wdf
where wdb.wstation_code=wdf.WSTATION_CODE and wdb.effect_code=wdf.EFFECT_CODE and wdb.date_time=wdf.DATE_TIME)),
wdb.update_time=sysdate where wdb.date_time >=(sysdate-2);
the aim is to update a table with values coming from one table or from another one depending on the existence of the data.
The reason for the error is that the pl/sql parser (before 9i) was different and usually "older" than the sql parser, and was not able to understand "new" syntax like the scalar subqueries that are used by your statement.
simply use the sql parser by writing
execute immediate 'update weather_data_buffer wdb set ...';
If your version doesn't even understand "execute immediate", use the equivalent procedure from the package dbms_sql.