Update with nested subqueries

I am having a problem with the following update statement:

UPDATE ST_WEEK_ST_ITEM
SET WEEK_AVG_QTY_SOLD_12 =
(

SELECT
CASE
  WHEN FIRST_SALE_DATE <= START_DATE THEN SUM(QTY_SOLD) / 12   ELSE SUM(QTY_SOLD) / ((END_DATE - FIRST_SALE_DATE) / 7) END WEEK_AVG FROM
(

SELECT
(SELECT MIN(WEEK_START_KEY) FROM SA_WEEK_ST_ITEM
--WHERE PRODUCT_KEY = ST_WEEK_ST_ITEM.PRODUCT_KEY AND SITE_KEY = ST_WEEK_ST_ITEM.SITE_KEY ) FIRST_SALE_DATE, WHERE PRODUCT_KEY = 266484 AND SITE_KEY = 43 ) FIRST_SALE_DATE,
(3432 - (11*7)) START_DATE,
(3432) END_DATE,

QTY_SOLD FROM SA_WEEK_ST_ITEM WHERE
--PRODUCT_KEY = ST_WEEK_ST_ITEM.PRODUCT_KEY AND SITE_KEY = ST_WEEK_ST_ITEM.SITE_KEY
PRODUCT_KEY = 266484 AND SITE_KEY = 43
AND WEEK_START_KEY BETWEEN 3432 - (11*7) AND 3432 )
GROUP BY FIRST_SALE_DATE, START_DATE, END_DATE )
WHERE WEEK_START_KEY = 3432 AND PRODUCT_KEY = 266484 AND SITE_KEY = 43 This query works as is, however I would like to insert the commented lines instead of hard coding product_key and site_key fields. Oracle however does not recognise the update table in the sub queries. Can anyone offer some help. Thanks Ian. Received on Fri Jul 30 2004 - 04:38:48 CDT