I'm creating a DataModel in Oracle Fusion Financials to match parties together, from supplier use and customer use. These parties have a code which is registered in their name. Searching for the table names on Google will find the schemas (eg. HZ_PARTIES), although it's not very necessary to see the schemas to tackle this issue.
Our data quality is not quite what we want it to be. To ensure I'm not missing records, I need to join on other parties who also have the code in the name.
This is what I have so far, which gives results.
SELECT
RCTA.TRX_NUMBER
,RCTA.CT_REFERENCE
,HP.PARTY_NAME PARTY_NAME1
,HP2.PARTY_NAME PARTY_NAME2
,IEBC.IBAN CUSTOMER_IBAN
FROM
HZ_PARTIES HP,
HZ_PARTIES HP2,
IBY_ACCOUNT_OWNERS IAO,
IBY_EXT_BANK_ACCOUNTS IEBC,
RA_CUSTOMER_TRX_ALL RCTA,
HZ_CUST_ACCOUNTS HCA
WHERE 1=1
AND RCTA.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID (+)
AND HCA.PARTY_ID = HP.PARTY_ID(+)
AND REGEXP_SUBSTR(HP.PARTY_NAME,'([0-9]{2}[A-Z]{2}[0-9]{3})') in REGEXP_SUBSTR(HP2.PARTY_NAME,'([0-9]{2}[A-Z]{2}[0-9]{3})') -- Join on code found in party name.
AND IAO.ACCOUNT_OWNER_PARTY_ID (+) IN (HP2.PARTY_ID)
AND IAO.EXT_BANK_ACCOUNT_ID = IEBC.EXT_BANK_ACCOUNT_ID (+)
However, this performs an inner join instead of the outer join I need.
I've tried the following, which gives a syntax error (missing parenthesis):
AND REGEXP_SUBSTR(HP.PARTY_NAME,'([0-9]{2}[A-Z]{2}[0-9]{3})') = REGEXP_SUBSTR(HP2.PARTY_NAME,'([0-9]{2}[A-Z]{2}[0-9]{3})') (+)
Also tried this, which makes the query run for way too long. Did not wait for results, because it's probably incorrect:
AND ( REGEXP_SUBSTR(HP.PARTY_NAME,'([0-9]{2}[A-Z]{2}[0-9]{3})') = REGEXP_SUBSTR(HP2.PARTY_NAME,'([0-9]{2}[A-Z]{2}[0-9]{3})') (+) -- Join on investor code found in party name.
OR NOT REGEXP_LIKE(HP.PARTY_NAME,'([0-9]{2}[A-Z]{2}[0-9]{3})') -- Escape to outer join in case there's no investor code in name
)
If it's necessary to make this work I'm willing to rewrite the (+) joins to regular outer join syntax.
You put outer join operator (+) to a wrong place. Should be something like this:
SQL> with
2 hp (party_name) as
3 (select '11AA111' from dual union all
4 select '22BB222' from dual
5 ),
6 hp2 (party_name) as
7 (select '11AA111' from dual union all
8 select '33CC333' from dual
9 )
10 select hp.*
11 from hp, hp2
12 where regexp_substr(hp.party_name , '([0-9]{2}[A-Z]{2}[0-9]{3})') =
13 regexp_substr(hp2.party_name (+), '([0-9]{2}[A-Z]{2}[0-9]{3})')
14 / ---
here
PARTY_N
-------
11AA111
22BB222
SQL>
As of proper joins ... well, yes - you could rewrite it if you want, but I don't think it'll help in this case. If query runs OK as is, I'd leave it as is and rewrite it if necessary.