Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Apex Interactive Grid with Join; Trying To Improve Performance

BaonergesJan 11 2022 — edited Jan 14 2022

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

This post has been answered by Baonerges on Jan 28 2022
Jump to Answer
Comments
Post Details
Added on Jan 11 2022
4 comments
2,279 views