Subquery Challenge

The following is an update statement that I created. The fields f1 and f2 are defined as NOT NULL. There is no guarantee that a matching record will be always found in table T2 and hence the first subquery is likely to return NULL values. This leads to errors since the fields f1 and f2 does not allow NULL values. In order to prevent this from happening I had to use the second subquery within the EXISTS clause.

update T1
set(f1,f2) =
(select sum(f4*f5), sum(f6) from T2
where T1.f6 = T3.f7)
where exists (select 1
where T1.f6 = T3.f7)

Is there a better way to write this? All ideas are welcome.

Thanks,

Murali
murali_at_jps.net Received on Mon Jul 27 1998 - 03:56:30 CDT