我正在尝试构建一个查询，该查询从Oracle及其笔记返回客户。不幸的是，我从中选择数据的注释表没有任何1-1加入客户，因此我通过使用派对ID并在包含客户合同号的注释中查找特定字符串加入数据。
我想做的是返回客户，合同及其票据信息，如果说明不存在。
我知道下面的代码很漫长，但是我对如何处理代码的最后一位特别感兴趣（因此，我在末尾加入注释信息的代码）。我在查询的当前版本中遇到的问题是，如果我通过将所有工会添加为nulls，则加入了force_note_guar和force_note_cust subquies，则性能非常糟糕。
如果我删除了该工会的所有表现都不错，但是我只会得到确实有笔记的客户，而我没有没有笔记的客户。
我知道这是一个很长的查询和一个很长的帖子，所以如果我能提供更多信息，请给我ping。
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));
用nulls删除unions，然后将查询更改为left join版本：
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||'%'