Re: PL/SQL : Probleme with not existence of result in temporary table

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 8 Mar 2003 13:47:58 GMT
Message-ID: <b4csad$1sit5q$1@ID-82536.news.dfncis.de>

> PL/SQL : Probleme with not existence of result in temporary table
>
> Hi, i have a problem with this request :
>
> update
> TABLE_U
> set field_u = (
> select PV.res
> from
> TABLE_1
> ,
> (
> SELECT
> CI4.cd_mkp,
> SUM(CI4.mt_pvp) res
> FROM
> INTRA_IFT_CAT CI4
> WHERE
> CI4.ind_netting = 0
> GROUP BY
> CI4.cd_mkp,
> ) TABLE_2
>
> where
> TABLE_1.val = TABLE_U.val
> AND TABLE_2.cd_mkp = TABLE_U.cd_mkp
> )
> where
> TABLE_1.val = TABLE_U.val
> AND TABLE_2.cd_mkp = TABLE_U.cd_mkp
> ;
>
> With this, i want this result :
>
> When 'res' exists, the field must be update whith PV.res.
> When 'res' doesn't exist (the request that generate TABLE_2 give no
> line), the field 'fild_u' must not be changed.
>
> In fact the resut is :
> When 'res' exists, the field must be update whith PV.res.
> When 'res' doesn't exist, the field 'fild_u' IS UPDATED BY NULL.

Which version of Oracle are you using? How do your tables look like? What data do they contain? Here's something for everybody posting questions related to SQL: Please add DDL statements (create table x (...)) and DML statements (insert into x values(...)) so as to make your case as easy reproducible as it can be.

Anyway, maybe this statment helps you:

update table_u set field_u = (
 select
   case when res is null then table_u.field_u else res end  from table_1, (

           select 
             cd_mkp,
             sum(mt_pvp) res
           from 
             intra_ift_cat
           group by 
             cd_mkp
           ) table_2

 where table_u.cd_mkp = table_2.cd_mkp
   and table_1.val = table_u.val)
where exists (
 select 1
 from table_1, (

           select 
             cd_mkp,
             sum(mt_pvp) res
           from 
             intra_ift_cat
           group by 
             cd_mkp
           ) table_2

 where table_u.cd_mkp = table_2.cd_mkp
   and table_1.val = table_u.val);

hth

Rene Nyffenegger

-- 
  Projektleitung und Entwicklung in Oracle/C++/C# Projekten
  http://www.adp-gmbh.ch/cv.html

Received on Sat Mar 08 2003 - 07:47:58 CST