Hi Everyone,
I am trying to write a join into my IG where I can still update one table and I am really struggling. I have always used just one table and the subqueries to get the information I need, but this specific query has grown so large, that I need to improve its performance.
Here is the error ORA-01779: cannot modify a column which maps to a non key-preserved table
I have seriously simplified the query below. There are two tables ORAPRODLIST and JOB. The primary keys are (JOB_NO, JOB_RELEASE) and (JOB_NO) respectively. So the records are Many to One. I make all modifications in ORAPRODLIST. The JOB table is only for information.
SELECT --ROWID,
PROMISED_DATE,
JOB_NO,
JOB_RELEASE,
JOB_RELEASE||'/'||MAX_RELEASE AS JOB_RELEASES,
HOLD_FLAG,
PROD_COMMENTS,
CSR_COMMENTS,
ITEM_CODE,
CUST_CODE||'<br>'||ITEM_CODE||'<br>'||WEBCODE AS ITEM,
CUST_CODE,
.
.
.
FROM
(
SELECT /*ROWID,*/ PROMISED_DATE, PREV_PROM_DATE, JOB_NO, JOB_RELEASE, MAX_RELEASE, HOLD_FLAG, PROD_COMMENTS, CSR_COMMENTS, CUST_CODE, ITEM_CODE, WEBCODE, ITEMCLASS, DESCRIPTION, BOOKED_DATE, ANTICIPATED_DATE, ORDER_DATE, JOB_STATUS,
.
.
.
FROM
(
--SELECT ORAPRODLIST.ROWID,
ORAPRODLIST.PROMISED_DATE,
ORAPRODLIST.JOB_NO,
JOB.JOB_NO AS JOBNO,
ORAPRODLIST.JOB_RELEASE,
ORAPRODLIST.ITEM_CODE,
ORAPRODLIST.CUST_CODE,
ORAPRODLIST.RELEASE_QTY,
ORAPRODLIST.UNIT_CODE,
ORAPRODLIST.HOLD_FLAG,
ORAPRODLIST.PROD_COMMENTS,
ORAPRODLIST.CSR_COMMENTS,
.
.
.
FROM ORAPRODLIST, LEGO.JOB WHERE ORAPRODLIST.JOB_NO = JOB.JOB_NO
)
WHERE ITEMCLASS <> 'Film' --(CSR <> 'AK' OR ITEMCLASS = 'Film-BGStk')
AND JOB_STATUS < 3
AND SUM_RELEASE - PRODUCED_QTY > 0
.
.
.
)
For example, this:
ITEMC_CODE AS ITEMCLASS,
JOB_TITLE1||'-'||JOB_TITLE2 AS DESCRIPTION,
JOB_BOOKED_DATE AS BOOKED_DATE,
JOB_ANTICIPATED_DATE AS ANTICIPATED_DATE,
JOB_PRODUCED_QTY AS PRODUCED_QTY,
JOB_ORDERED_QTY AS ORDERED_QTY,
EMP_CODE_IREP AS CSR,
used to be this:
(SELECT ITEMC_CODE FROM LEGO.JOB WHERE JOB_NO = ORAPRODLIST.JOB_NO) AS ITEMCLASS,
(SELECT JOB_TITLE1||'-'||JOB_TITLE2 FROM LEGO.JOB WHERE JOB_NO = ORAPRODLIST.JOB_NO) AS DESCRIPTION,
(SELECT JOB_BOOKED_DATE FROM LEGO.JOB WHERE JOB_NO = ORAPRODLIST.JOB_NO) AS BOOKED_DATE,
(SELECT JOB_ANTICIPATED_DATE FROM LEGO.JOB WHERE JOB_NO = ORAPRODLIST.JOB_NO) AS ANTICIPATED_DATE,
(SELECT JOB_PRODUCED_QTY FROM LEGO.JOB WHERE JOB_NO = ORAPRODLIST.JOB_NO) AS PRODUCED_QTY,
(SELECT JOB_ORDERED_QTY FROM LEGO.JOB WHERE JOB_NO = ORAPRODLIST.JOB_NO) AS ORDERED_QTY,
(SELECT EMP_CODE_IREP FROM LEGO.JOB WHERE JOB_NO = ORAPRODLIST.JOB_NO) AS CSR,
How do I join these tables in a way that allows me to modify ORAPRODLIST? Since Performance is the original goal, I am open to any other suggestions.
EDIT: More accurate simplified query