I created a Global temporary table:
CREATE GLOBAL TEMPORARY TABLE gtt_cpr_tmp_pricing
(
contract_nbr number not null,
pricing_order number not null,
priced_from_contract_flag char(1) not null,
release_nbr number not null,
quantity number(25,6) not null,
price number(18,6) null,
price_future number(18,6) null,
price_basis number(18,6) null,
sum_qty number(25,6) null,
row_nbr number null,
new_quantity number(25,6) null
)
ON COMMIT DELETE ROWS
tablespace temp
/
I was trying to run this SQL
UPDATE
(SELECT gtt_cpr_tmp_pricing.sum_qty,
t1_sum_qty
FROM gtt_cpr_tmp_pricing
JOIN (SELECT contract_nbr,
SUM(quantity) AS t1_sum_qty
FROM gtt_cpr_tmp_pricing
GROUP BY contract_nbr
) t1
ON gtt_cpr_tmp_pricing.contract_nbr = t1.contract_nbr
) updateTable
SET updateTable.sum_qty = updateTable.t1_sum_qty;
It produces this error:
Error report -
SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
01779. 00000 - "cannot modify a column which maps to a non key-preserved table"
*Cause: An attempt was made to insert or update columns of a join view which
map to a non-key-preserved table.
*Action: Modify the underlying base tables directly.
I tried to rewrite the SQL as a MERGE statement:
MERGE INTO gtt_cpr_tmp_pricing t
USING (SELECT t.rowid as row_id,
sum_qty,
t1_sum_qty
FROM gtt_cpr_tmp_pricing t
JOIN (SELECT rowid,
contract_nbr,
SUM(quantity) AS t1_sum_qty
FROM gtt_cpr_tmp_pricing
GROUP BY contract_nbr
)
) t1
ON t.rowid = t1.rowid
WHEN MATCHED
THEN UPDATE SET sum_qty = t1_sum_qty;
It also produces an error that I don't understand:
Error report -
SQL Error: ORA-00905: missing keyword
00905. 00000 - "missing keyword"
*Cause:
*Action:
The line it at is producing the error is this line (at least that's the line that is underlined (in red)):
) t1
Thanks
Murray