I am trying to build a query that returns the customers from Oracle and their notes. Unfortunately, the notes tables from which I am selecting the data do not have any 1-1 joins with the customers so I am joining the data by using the party id and looking for a particular string in the notes that contain the customer contract number.
What I want to do is to return the customer, contract and their note information if the notes exist and if the notes do not exist.
I know the code below is lengthy, but I am particularly interested in how to handle the very last bit of the code (so the code where I join with the notes info at the end). The issue that I have in the current version of the query is that if I join the FORCE_NOTE_GUAR and FORCE_NOTE_CUST subqueries by adding the UNION ALL with nulls, the performance is very very bad.
If I remove that UNION ALL the performance is good, however I only get the customers that do have the notes and I don't have the customers that do not have the notes.
I know that it is a long query and a long post so please ping me if I can give more info.
SELECT QUERY_MAIN.*
, FORCE_NOTE_CUST.NOTE_CREATION_DATE AS FORCE_ACCEPT_DATE_CUST
, FORCE_NOTE_GUAR.NOTE_CREATION_DATE AS FORCE_ACCEPT_DATE_GUAR
, FORCE_NOTE_CUST.ENTERED_BY_NAME AS USER_FORCE_ACCEPT_CUST
, FORCE_NOTE_GUAR.ENTERED_BY_NAME AS USER_FORCE_ACCEPT_GUAR
, FORCE_NOTE_CUST.NOTES AS NOTES_CUST
, FORCE_NOTE_GUAR.NOTES AS NOTES_GUAR
FROM (SELECT HP.PARTY_ID
, HCA_CUSTOMER.ACCOUNT_NUMBER AS ACCOUNT_NUMBER
, OKH.CONTRACT_NUMBER AS CONTRACT_NUMBER
, DECODE(OKP.ATTRIBUTE5, 'F', 'Y', 'N') AS CUSTOMER_FORCE
, DECODE(GUAR_FORCE.FORCE_FLAG, 'F', 'Y', 'N') AS GUARANTOR_FORCE
--------------------------------------------------------------------------
FROM ... customer tables) QUERY_MAIN
--------------------------------------------------------------------------------
, (SELECT* FROM(SELECT JII.PARTY_ID AS PARTY_ID
, TO_CHAR(DECODE( JIHA.ACTION, 'Converted'
, SUBSTR(JNV.NOTES_DETAIL,1,2000)
, NVL( JNV.NOTES
, SUBSTR( JNV.NOTES_DETAIL
, 1
, 2000)))) AS NOTES
, JNV.CREATION_DATE AS NOTE_CREATION_DATE
, NVL(PEP.FULL_NAME, FU_INT.USER_NAME) AS ENTERED_BY_NAME
----------------------------------------------------------------
FROM ... notes tables)
WHERE NOTES LIKE '%Guarantor acceptance manually progressed%'
UNION ALL
SELECT NULL AS PARTY_ID
, NULL AS NOTES
, NULL AS NOTE_CREATION_DATE
, NULL AS ENTERED_BY_NAME
FROM DUAL) FORCE_NOTE_GUAR
--------------------------------------------------------------------------------
, (SELECT* FROM(SELECT JII.PARTY_ID AS PARTY_ID
, TO_CHAR(DECODE( JIHA.ACTION, 'Converted'
, SUBSTR(JNV.NOTES_DETAIL,1,2000)
, NVL( JNV.NOTES
, SUBSTR( JNV.NOTES_DETAIL
, 1
, 2000)))) AS NOTES
, JNV.CREATION_DATE AS NOTE_CREATION_DATE
, NVL(PEP.FULL_NAME, FU_INT.USER_NAME) AS ENTERED_BY_NAME
----------------------------------------------------------------
FROM ... notes tables)
WHERE NOTES LIKE '%Customer acceptance manually progressed%'
UNION ALL
SELECT NULL AS PARTY_ID
, NULL AS NOTES
, NULL AS NOTE_CREATION_DATE
, NULL AS ENTERED_BY_NAME
FROM DUAL) FORCE_NOTE_CUST
--------------------------------------------------------------------------------
-- Outer logic to select the appropriate notes
WHERE 1 = 1
AND (( CUSTOMER_FORCE = 'N' AND FORCE_NOTE_CUST.PARTY_ID IS NULL)
--If CUSTOMER_FORCE = 'Y'
--If the customer has force accepted, we need to find the note
OR ( CUSTOMER_FORCE = 'Y'
AND QUERY_MAIN.PARTY_ID = FORCE_NOTE_CUST.PARTY_ID
AND INSTR(FORCE_NOTE_CUST.NOTES, CONTRACT_NUMBER) > 0))
AND (( GUARANTOR_FORCE = 'N' AND FORCE_NOTE_GUAR.PARTY_ID IS NULL)
--If GUARANTOR_FORCE = 'Y'
--If the guarantor has force accepted, we need to find the note
OR ( GUARANTOR_FORCE = 'Y'
AND QUERY_MAIN.PARTY_ID = FORCE_NOTE_GUAR.PARTY_ID
AND INSTR(FORCE_NOTE_GUAR.NOTES, CONTRACT_NUMBER) > 0));
Remove unions with nulls and change Your query to left join version:
SELECT QUERY_MAIN.*,
FORCE_NOTE_CUST.NOTES,
FORCE_NOTE_GUAR.NOTES
FROM QUERY_MAIN
LEFT JOIN FORCE_NOTE_GUAR on FORCE_NOTE_CUST.PARTY_ID = QUERY_MAIN.PARTY_ID
and FORCE_NOTE_CUST.NOTES like '%'||CONTRACT_NUMBER||'%'
LEFT JOIN FORCE_NOTE_CUST on FORCE_NOTE_GUAR.PARTY_ID = QUERY_MAIN.PARTY_ID
and FORCE_NOTE_GUAR.NOTES like '%'||CONTRACT_NUMBER||'%'