Results 1 to 2 of 2

Thread: NVL in a update

  1. #1
    Join Date
    Jan 2004

    Unanswered: NVL in a update


    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.

    Thanks for your help

  2. #2
    Join Date
    Sep 2003
    Milan, Italy
    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts