Issue with the Join order

Database
Enthusiast

Issue with the Join order

Hi,

I have to insert some missing acct_id from one table to another. Along with this i have some other columns as well which i want to insert. The issue is with the order of joins.

The no of acct ids which are missing is close to 1.7 million from the below query:

 

SELECT ACCT_ID 
FROM   X.ACCT_SUBS_HIST  A 
WHERE  NOT EXISTS (
   SELECT 1             
   FROM   X.PARTY_ACCT_HIST B 
   WHERE  A.ACCT_ID = B.ACCT_ID
   )

I am using the below query but not getting actual counts. My requirement is to get all the acct ids of above query and not matching other columns can be null. I am using the below query.

 

SELECT 
PI.PARTY_ID
,ACCT_SB_HIST.ACCT_ID
,CASE WHEN S_ORG.ACTIVE_FLG='Y' THEN 'OA'
ELSE 'OE' END AS PARTY_ACCT_ROLE_CD

FROM X.PARTY_IDENTIFICATION  PI
LEFT OUTER JOIN  DP_EDW_REP_SIEBEL.S_ORG_EXT S_ORG
ON PI.PARTY_IDENTIFICATION_ROW_ID=S_ORG.PAR_ROW_ID
LEFT OUTER JOIN X.ACCT ACCT
ON ACCT.ACCT_NUM=S_ORG.OU_NUM
LEFT OUTER JOIN X.ACCT_SUBS_HIST ACCT_SB_HIST
ON ACCT.ACCT_ID=ACCT_SB_HIST.ACCT_ID
LEFT OUTER  JOIN X.PARTY_ACCT_HIST PACCT_HIST
ON  ACCT_SB_HIST.ACCT_ID = PACCT_HIST.ACCT_ID
WHERE  PACCT_HIST.ACCT_ID IS NULL 

can someone help me with the approach.

1 REPLY
Teradata Employee

Re: Issue with the Join order

I'm not sure why that doesn't work, but perhaps the joins are affecting the results.

 

You could try the original relational SQL syntax, using the MINUS set operator:

 

SELECT PI.PARTY_ID
    ,ACCT_SB_HIST.ACCT_ID
    ,CASE WHEN S_ORG.ACTIVE_FLG='Y' THEN 'OA' ELSE 'OE' END AS PARTY_ACCT_ROLE_CD
FROM X.PARTY_IDENTIFICATION PI
LEFT OUTER JOIN DP_EDW_REP_SIEBEL.S_ORG_EXT S_ORG ON   PI.PARTY_IDENTIFICATION_ROW_ID=S_ORG.PAR_ROW_ID
LEFT OUTER JOIN X.ACCT ACCT ON  ACCT.ACCT_NUM=S_ORG.OU_NUM
LEFT OUTER JOIN X.ACCT_SUBS_HIST ACCT_SB_HIST ON  ACCT.ACCT_ID=ACCT_SB_HIST.ACCT_ID
MINUS
SELECT PACCT_HIST.ACCT_ID, cast(null as INT), cast(null as char(2))
FROM X.PARTY_ACCT_HIST PACCT_HIST

 

Then try eliminating some joins or putting them in subqueries so you can see what joiin gives you what data.