CODEWITHSUNDEEP
sqloracle-database
user1009073
user1009073

Reputation: 3238

How to traverse a table in Oracle SQL

I have a table in Oracle (12c) which I need to retrieve data.
The table is called ACCOUNT_ANALYSIS_FY16. This table holds a 3 level hierarchy of businesses. The position in this hierarchy is designated by the column DUNS_LEVEL, whose value is DUNS (meaning the lowest level of a business), IMMEDIATE (meaning a level 2, aka Parent of DUNS level) or ULTIMATE, (meaning level 3, or Parent of IMMEDIATE).

Applicable columns are:
NN_NAME - Account Name
DUNS_LEVEL
DUNS
ULTIMATE_PARENT_DUNS
IMMEDIATE_PARENT_DUNS

The requirement... for a given account name (and there may be multiple occurrences for the same account name due to different locations), I need to see everything about that account (aka SELECT *), AS WELL AS everything about the Immediate Parent and the Ultimate Parent of the given account.

I have written this in SQL, but it involves 5 SELECT statements. I know there has to be a more elegant and simple way to do this. Note that the end result is to place this in an anonymous PL/SQL block, so it is acceptable to write a PL/SQL block if that is a better solution.

Here is the straight SQL that I have which works...

Select *from ACCOUNT_ANALYSIS_FY16 where NN_NAME = 'XYZ GROUP'
union
Select * from ACCOUNT_ANALYSIS_FY16 where DUNS in (Select IMMEDIATE_PARENT_DUNS from ACCOUNT_ANALYSIS_FY16 where NN_NAME = 'XYZ GROUP')
union
Select * from ACCOUNT_ANALYSIS_FY16 where DUNS in (Select ULTIMATE_PARENT_DUNS from ACCOUNT_ANALYSIS_FY16 where NN_NAME = 'XYZ GROUP')

Thanks

Upvotes: 1

Views: 731

Answers (1)

krokodilko
krokodilko

Reputation: 36107

Try:

SELECT *
FROM ACCOUNT_ANALYSIS_FY16 t
WHERE NN_NAME = 'XYZ GROUP'
  OR EXISTS (
     SELECT NULL 
     from ACCOUNT_ANALYSIS_FY16 t1
     WHERE t.DUNS IN ( t1.ULTIMATE_PARENT_DUNS, t1.IMMEDIATE_PARENT_DUNS )
       AND t1.NN_NAME = 'XYZ GROUP'
  )

Upvotes: 1

Related Questions