recursive oracle sql to identify a value

Question

I have a table TABA(PK:NAME) that stores NAME,NAME_TYPE,SOURCE this way:

NAME  NAME_TYPE SOURCE
----  --------- ------
Name1 Category  S1
Name2 Category  S2
Name3 Datamart  
Name4 Category  S1
Name5 Datamart
Name6 Datamart
Name7 Category  S3

Note above: Only if the NAME_TYPE = Category, then the source is present in the data.

I have another table TABA_PARENT that stores NAME,PARENT_NAME with the relationship between the NAME columns based on the name_type this way. Datamart to Category relationship is one-to-many.

  NAME   PARENT_NAME
    -----  -----------
    Name3  Name1
    Name3  Name2
    Name3  Name4
    Name3  Name5
    Name5  Name1
    Name5  Name6
    Name6  Name7

My requirement is to get the SOURCE for TABA where NAME_TYPE = Datamart(currently, it is absent in TABA)

Expected Output:

SOURCE column for Name3
-----------------------

S1,S2,S3

The trick is to recursively deduce the SOURCE for the Name3 until it maps to a Category in TABA_PARENT.

In the above example:

Name3 is mapped to PARENT_NAME Name1,Name2,Name4,Name5. 3 of these(name1,Name2,Name4) are of name_type = Category and hence the distinct source is available in TABA -- S1,S2 The fourth PARENT_NAME Name5 is a name_type Datamart(source info not available) and needs to be further expanded until it reaches name_type = Category.

We have the information that Name5 is mapped to PARENT_NAME Name1,Name6. Name1 is a Category and hence source can be deduced. Name6 is again a Datamart.

But, Name6 ultimately is mapped to Name7 which is a Category, hence source is available -- S3

As shown above, all the mappings have to be recursively resolved until they reach name_type Category to identify the distinct sources.

Expected Result: S1,S2,S3 

I am trying if this can be done using listagg or something similar(small pl/sql code would also be ok, but prefer a sinlge select if possible) I am having difficulty to recursively do this. Any help would be much appreciated.

Answer 1

As noted in the comments this can be done with a combination of listagg(), which is available from Oracle 11.2 onwards and connect by. If you're not using 11.2 then there are a number of other string aggregation techniques available.

select listagg(source, ',' ) within group ( order by source )
  from ( select distinct source
           from taba a
           join ( select parent_name
                    from taba_parent
                   start with name = 'Name3'
                 connect by prior parent_name = name
                         ) b
             on a.name = b.parent_name
                )

The distinct sub-query is only there because you have multiple sources that are the same. This returns S1,S2,S3.

In order to obtain the same for different names you can change the START WITH clause; for instance changing this to start with name = 'Name5' returns S1,S3.

The fact that a datamart doesn't have a source doesn't matter as you use the hierarchical query on the taba_parent table only, joining to the taba table only when you have the information you need.

Here's a little SQL Fiddle to demonstrate.

Answer 2

Thanks for anyone attempting this. I tried to use a combination of "connect by" and listagg and achieved the desired result.

select listagg(source,',') within group (order by source) final_source from ( 
select
distinct
b.source source--,
from taba_parent a, taba b
where b.name = a.parent_name
and b.name_type = 'Category'
connect by prior a.parent_name = a.name
start with a.name = 'Name3'        
);

Answer 3

You need a hierarchical query (connect by) to get your "ultimate parent", then you need to use listagg to concatenate the items together. I only have Oracle 10g, so I don't have listagg. Here's the hierarchical bit:

select distinct source from
(
select taba_parent.name, taba_parent.parent_name, taba.source
from
taba_parent
inner join taba on taba_parent.parent_name = taba.name
)
where name in (select name from taba where name_type = 'Datamart')
connect by name = parent_name
start with source is not null

This gives:

SOURCE
S3
S2
S1

And you can use listagg to get S3, S2, S1