[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 870
  • Last Modified:

Oracle - Connect by Prior - Two times in single query

Hi!

I am working on Oracle. I have two tables with recursive relationships. I have to derive a Hierarchial View from both these tables in a single view.

For this, i need to use CONNECT BY PRIOR statement twice. How is this achievable?

Thanks in advance.
0
Adwait Chitaley
Asked:
Adwait Chitaley
1 Solution
 
Shaju KumbalathCommented:
0
 
shru_0409Commented:
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ravibhardwajCommented:
Hi,
Can you give the example. l.e
how your table look like and how u want the output.
0
 
Adwait ChitaleyAuthor Commented:
Thanks for the inputs ; however i require more precise solution. Consider this:

a) A Table named REGION TYPE which contains a recursive relationship. The data in Region Type table:

Region Type ID | Region Type Parent ID | Region Type Name
1                         |        Null                           |         Continent
2                         |        1                                |         Country
3                         |        2                                 |          State
4                         |        3                                 |           District
5                         |        4                                 |             Town


Now, the Region Type ID is referred to in another table named REGIONS, which itself is another table with a recursive relationship. The REGIONS table will contain the following data:


Region ID |  Region Parent ID | Region Type ID (FK) | Region Name
1                |          Null                |            1                       | Asia
2                |           1                    |            2                      | India
3                 |           2                    |            3                      | Tamil Nadu
4                 |           3                    |            4                      | Chennai District
5                 |           4                    |             5                    | Chennai

The desired view is from both the tables in the following format:


Region ID  | Region Type ID  | Region Hierarchy | Region Type Hierarchy
                 
== Only one record sampled===

5                 |         5                  | Asia -> India -> Tamil Nadu -> Chennai District-> Chennai   |  Continent -> Country -> State -> District -> Town


In the above view, Connect by Prior will be required to derive the last two fields : Region Hierarchy and Regions Hierarchy.

My query was: How can I derive them in a single view? Is it necessary to use two views for this purpose?

Thanks in advance.


0
 
shru_0409Commented:
select rid , LTRIM (EXTRACT (XMLAGG (XMLELEMENT ("x", ' , ' || REGION_NAME)), '/x/text()'),',' ) disc from (
select (select max (REGION_ID) from REGIONS) rid,  REGION_NAME  
from REGIONS  
start with REGION_PARENT_ID is null
connect by prior REGION_ID =  REGION_PARENT_ID
union all
select (select max (REGION_TYPE_ID) from REGION_TYPE ) rid,  REGION_TYPE_NAME  
from REGION_TYPE z
start with REGION_TYPE_PARENT_ID  is null
connect by prior REGION_TYPE_ID  =  REGION_TYPE_PARENT_ID
order by 1 )
group by rid

try this
0
 
Shaju KumbalathCommented:
select region_id,type_id,ltrim(sys_connect_by_path( TYPE_NAME, ',' ),',') region_type,ltrim(sys_connect_by_path(REGION_NAME , ',' ),',') from region_type a, regions b where a.type_id=b.region_type_id and type_id=5
start with PARENT_TYPE_ID is null
connect by prior TYPE_ID = PARENT_TYPE_ID;
 
 

CREATE TABLE REGION_TYPE
(
TYPE_ID NUMBER(2),
PARENT_TYPE_ID NUMBER(2),
TYPE_NAME VARCHAR2(15 BYTE)
)
;
 
 
Insert into REGION_TYPE
(TYPE_ID, PARENT_TYPE_ID, TYPE_NAME)
Values
(1, NULL, 'Continent');
Insert into REGION_TYPE
(TYPE_ID, PARENT_TYPE_ID, TYPE_NAME)
Values
(2, 1, 'Country');
Insert into REGION_TYPE
(TYPE_ID, PARENT_TYPE_ID, TYPE_NAME)
Values
(3, 2, 'State');
Insert into REGION_TYPE
(TYPE_ID, PARENT_TYPE_ID, TYPE_NAME)
Values
(4, 3, 'District');
Insert into REGION_TYPE
(TYPE_ID, PARENT_TYPE_ID, TYPE_NAME)
Values
(5, 4, 'Town');
COMMIT;
 
 

CREATE TABLE REGIONS
(
REGION_ID NUMBER(2),
REGION_PARENT_ID NUMBER(2),
REGION_TYPE_ID NUMBER(2),
REGION_NAME VARCHAR2(15 BYTE)
) ;
 
 
Insert into REGIONS
(REGION_ID, REGION_PARENT_ID, REGION_TYPE_ID, REGION_NAME)
Values
(1, NULL, 1, 'Asia');
Insert into REGIONS
(REGION_ID, REGION_PARENT_ID, REGION_TYPE_ID, REGION_NAME)
Values
(2, 1, 2, 'India');
Insert into REGIONS
(REGION_ID, REGION_PARENT_ID, REGION_TYPE_ID, REGION_NAME)
Values
(3, 2, 3, 'Tamil Nadu');
Insert into REGIONS
(REGION_ID, REGION_PARENT_ID, REGION_TYPE_ID, REGION_NAME)
Values
(4, 3, 4, 'Chennai Dist');
Insert into REGIONS
(REGION_ID, REGION_PARENT_ID, REGION_TYPE_ID, REGION_NAME)
Values
(5, 4, 5, 'Chennai');
COMMIT;
0
 
Shaju KumbalathCommented:
or

select region_id,type_id,ltrim(sys_connect_by_path( TYPE_NAME, ',' ),',') region_type,ltrim(sys_connect_by_path(REGION_NAME , ',' ),',') from region_type a, regions b where a.type_id=b.region_type_id
start with PARENT_TYPE_ID is null
connect by prior TYPE_ID = PARENT_TYPE_ID;
for getting all records
0
 
Shaju KumbalathCommented:
there was an error in the code when we have multiple records in region table for each type
try this

select region_id,type_id,ltrim(sys_connect_by_path( TYPE_NAME, ',' ),',') region_type,ltrim(sys_connect_by_path(REGION_NAME , ',' ),',') from region_type a, regions b where a.type_id=b.region_type_id
start with REGION_PARENT_ID is null
connect by prior REGION_ID = REGION_PARENT_ID;  
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now