UPDATE with DECODE in subquery

I have an update statement that is not producing the expected results. The subquery of the update statement has a DECODE, and I think that is where the problem is occurring. There are (3) possible values for a project description...

1. If a description already exists, keep it
2. If a description does not exist, but a description is in the temporary table, then use the description in the temporary table.
3. If a description does not exist, and a description is not in the temporary table, then use the site name as the description

If this subquery is run independently (as a SELECT statement in SQL*Plus), it succeeds
in making the right "selection" for each of the (3) conditions. However, when it is embedded as a
subquery, it ends up setting existing project descriptions to NULL. Obviously, that is a major problem because users are seeing existing descriptions "disappear".

Here is the UPDATE statement with subquery:

UPDATE CP_PROJECT a
SET PROJ_DESC =
(SELECT
DECODE(a.PROJ_DESC,NULL,DECODE(b.PROJ_DESC,NULL,c.SITE_NAME,b.PROJ_DESC) ,a.PROJ_DESC)
FROM CP_SITE c,CP_ORACLE_PROJECT b
WHERE a.PROJ_NO = c.PROJ_NO(+)
AND a.PROJ_NO = b.PROJ_NO(+)
AND c.SITE_STATUS(+) = 'P'
)
;

Any help will be greatly appreciated,

--
Brian in Seattle
Pride, commitment, teamwork - words we use to get you to work for free.

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Aug 12 1999 - 10:34:29 CDT