sql query explanation

Question

The following is the query being used to select data from a source table STG01_ACCOUNT_EBS_SRC_DUP to a destination table STG02_ACCOUNT_F_DUP. I am facing a hard time in visualizing the data when these joints are being executed.

-My understanding is TEN table instances have been created from P1 to P10 and ELEVEN column aliases are created from G1 to G11.

insert into STG02_ACCOUNT_F_DUP 
select distinct '10' HIER_ID, 'EXPIT' HIER_NAME, P1.PARENT_FLEX_VALUE G1, 
P1.FLEX_VALUE G2, P1.SUMMARY_FLAG_ORG, P1.SUMMARY_FLAG_CLS, P1.RANGE_ATTRIBUTE, P1.ENABLED_FLAG, 
P2.FLEX_VALUE G3, P2.SUMMARY_FLAG_ORG, P2.SUMMARY_FLAG_CLS, P2.RANGE_ATTRIBUTE, P2.ENABLED_FLAG, 
P3.FLEX_VALUE G4, P3.SUMMARY_FLAG_ORG, P3.SUMMARY_FLAG_CLS, P3.RANGE_ATTRIBUTE, P3.ENABLED_FLAG, 
P4.FLEX_VALUE G5, P4.SUMMARY_FLAG_ORG, P4.SUMMARY_FLAG_CLS, P4.RANGE_ATTRIBUTE, P4.ENABLED_FLAG, 
P5.FLEX_VALUE G6, P5.SUMMARY_FLAG_ORG, P5.SUMMARY_FLAG_CLS, P5.RANGE_ATTRIBUTE, P5.ENABLED_FLAG, 
P6.FLEX_VALUE G7, P6.SUMMARY_FLAG_ORG, P6.SUMMARY_FLAG_CLS, P6.RANGE_ATTRIBUTE, P6.ENABLED_FLAG, 
P7.FLEX_VALUE G8, P7.SUMMARY_FLAG_ORG, P7.SUMMARY_FLAG_CLS, P7.RANGE_ATTRIBUTE, P7.ENABLED_FLAG, 
P8.FLEX_VALUE G8, P8.SUMMARY_FLAG_ORG, P8.SUMMARY_FLAG_CLS, P8.RANGE_ATTRIBUTE, P8.ENABLED_FLAG, 
P9.FLEX_VALUE G10, P9.SUMMARY_FLAG_ORG, P9.SUMMARY_FLAG_CLS, P9.RANGE_ATTRIBUTE, P9.ENABLED_FLAG, 
P10.FLEX_VALUE G11, P10.SUMMARY_FLAG_ORG, P10.SUMMARY_FLAG_CLS, P10.RANGE_ATTRIBUTE, P10.ENABLED_FLAG, 
SYSDATE
from STG01_ACCOUNT_EBS_SRC_DUP P1, STG01_ACCOUNT_EBS_SRC_DUP P2, 
STG01_ACCOUNT_EBS_SRC_DUP P3, STG01_ACCOUNT_EBS_SRC_DUP P4, 
STG01_ACCOUNT_EBS_SRC_DUP P5, STG01_ACCOUNT_EBS_SRC_DUP P6, 
STG01_ACCOUNT_EBS_SRC_DUP P7, STG01_ACCOUNT_EBS_SRC_DUP P8, 
STG01_ACCOUNT_EBS_SRC_DUP P9, STG01_ACCOUNT_EBS_SRC_DUP P10
where P1.FLEX_VALUE = P2.PARENT_FLEX_VALUE (+)
and P2.FLEX_VALUE = P3.PARENT_FLEX_VALUE (+)
and P3.FLEX_VALUE = P4.PARENT_FLEX_VALUE (+)
and P4.FLEX_VALUE = P5.PARENT_FLEX_VALUE (+)
and P5.FLEX_VALUE = P6.PARENT_FLEX_VALUE (+)
and P6.FLEX_VALUE = P7.PARENT_FLEX_VALUE (+)
and P7.FLEX_VALUE = P8.PARENT_FLEX_VALUE (+)
and P8.FLEX_VALUE = P9.PARENT_FLEX_VALUE (+)
and P9.FLEX_VALUE = P10.PARENT_FLEX_VALUE (+)
and P1.PARENT_FLEX_VALUE = '80000'
order by P1.PARENT_FLEX_VALUE, P1.FLEX_VALUE, P2.FLEX_VALUE, P3.FLEX_VALUE, P4.FLEX_VALUE, 
P5.FLEX_VALUE, P6.FLEX_VALUE, P7.FLEX_VALUE, P8.FLEX_VALUE,
P9.FLEX_VALUE, P10.FLEX_VALUE;

Could you explain what's happening in this block by using LEFT-JOIN ?

 where P1.FLEX_VALUE = P2.PARENT_FLEX_VALUE (+)
    and P2.FLEX_VALUE = P3.PARENT_FLEX_VALUE (+)
    and P3.FLEX_VALUE = P4.PARENT_FLEX_VALUE (+)
    and P4.FLEX_VALUE = P5.PARENT_FLEX_VALUE (+)
    and P5.FLEX_VALUE = P6.PARENT_FLEX_VALUE (+)
    and P6.FLEX_VALUE = P7.PARENT_FLEX_VALUE (+)
    and P7.FLEX_VALUE = P8.PARENT_FLEX_VALUE (+)
    and P8.FLEX_VALUE = P9.PARENT_FLEX_VALUE (+)
    and P9.FLEX_VALUE = P10.PARENT_FLEX_VALUE (+)
    and P1.PARENT_FLEX_VALUE = '80000'

Thanks!!

Each STG01_ACCOUNT_EBS_SRC_DUP can be linked to his sub STG01_ACCOUNT_EBS_SRC_DUP by the field FLEX_VALUE matchin his child PARENT_FLEX_VALUE. So just by reading it you Start with : P1.PARENT_FLEX_VALUE = '80000' the son of '80000' and then you take all the son of this one. — Drag and Drop, Jun 13 '17 at 06:09
Are you asking for the [`(+)`](https://stackoverflow.com/questions/4217293/what-does-a-sign-mean-in-an-oracle-sql-where-clause?noredirect=1&lq=1) ? It's old oracle syntax for outer join — Drag and Drop, Jun 13 '17 at 06:09
Even Oracle recommends to stop using the outdated `(+)` operator for outer joins. You should use a `left join` instead — , Jun 13 '17 at 06:10
Side note: 1 /. Remove the `(+)` 2/. Check if you can have deeper hierachy than 10 or the 10 is mandotory 3/. Translate this recursion with a CTE for cleanner code. — Drag and Drop, Jun 13 '17 at 06:21

score 0 · Accepted Answer · answered Jun 13 '17 at 06:11

Do you know what a left join is?

where P1.FLEX_VALUE = P2.PARENT_FLEX_VALUE (+)

means: Always return a record from P1 whether or not a matching record exists in P2, If there is no matching record in P2, return NULLs. See here: Oracle "(+)" Operator If that doesn't answer your question, you need to explain further.

To put it another way, this is working it's way up a tree, and if there is no parent in the tree, still include the record.

If you to remove the (+), then your query would probably return very littlt

This made me understand better now. – Avi Jun 15 '17 at 09:37 — Avi, Jun 15 '17 at 09:37

sql query explanation

1 Answers1