Sql Hierarchy query with two tables

Question

I have two separate oracle tables and each has a hierarchy. They are related by the ACCOUNT_TYPE key. Table 1 definition is as such.

CREATE TABLE ACCOUNTS(
    ACCOUNT_CODE VARCHAR2(6),
    ACCOUNT_PRED VARCHAR2(6),
    ACCOUNT_TYPE VARCHAR2(6),
    ACCOUNT_TITLE VARCHAR2(100)
    );

Table 2 definition is as such

 CREATE TABLE ACCOUNT_TYPES(
    ACCOUNT_TYPE  VARCHAR2(6),
    ACCOUNT_NUMBER_PRED VARCHAR2(6),
    ACCOUNT_TITLE VARCHAR(100)
    );

Table 1 contains the following data

Insert into HR.ACCOUNTS (ACCOUNT_CODE,ACCOUNT_PRED,ACCOUNT_TYPE,ACCOUNT_TITLE) values ('0001',null,'11','xxxx');
Insert into HR.ACCOUNTS (ACCOUNT_CODE,ACCOUNT_PRED,ACCOUNT_TYPE,ACCOUNT_TITLE) values ('0042','0070','13','xxxx');
Insert into HR.ACCOUNTS (ACCOUNT_CODE,ACCOUNT_PRED,ACCOUNT_TYPE,ACCOUNT_TITLE) values ('0054','0110','13','xxxx');
Insert into HR.ACCOUNTS (ACCOUNT_CODE,ACCOUNT_PRED,ACCOUNT_TYPE,ACCOUNT_TITLE) values ('0056','0070','13','xxxx');
Insert into HR.ACCOUNTS (ACCOUNT_CODE,ACCOUNT_PRED,ACCOUNT_TYPE,ACCOUNT_TITLE) values ('0070',null,'13','xxxx');
Insert into HR.ACCOUNTS (ACCOUNT_CODE,ACCOUNT_PRED,ACCOUNT_TYPE,ACCOUNT_TITLE) values ('0110',null,'13','xxxx');
Insert into HR.ACCOUNTS (ACCOUNT_CODE,ACCOUNT_PRED,ACCOUNT_TYPE,ACCOUNT_TITLE) values ('0172','0171','13','xxxx');
Insert into HR.ACCOUNTS (ACCOUNT_CODE,ACCOUNT_PRED,ACCOUNT_TYPE,ACCOUNT_TITLE) values ('0060','0001','11','XXXX');

table two contains the following data

REM INSERTING into ACCOUNT_TYPES
SET DEFINE OFF;
Insert into ACCOUNT_TYPES (ACCOUNT_TYPE,ACCOUNT_NUMBER_PRED,ACCOUNT_TITLE) values ('10',null,'xxxx');
Insert into ACCOUNT_TYPES (ACCOUNT_TYPE,ACCOUNT_NUMBER_PRED,ACCOUNT_TITLE) values ('11','10','xxxx');
Insert into ACCOUNT_TYPES (ACCOUNT_TYPE,ACCOUNT_NUMBER_PRED,ACCOUNT_TITLE) values ('12','10','xxxx');
Insert into ACCOUNT_TYPES (ACCOUNT_TYPE,ACCOUNT_NUMBER_PRED,ACCOUNT_TITLE) values ('13','10','xxxx');
Insert into ACCOUNT_TYPES (ACCOUNT_TYPE,ACCOUNT_NUMBER_PRED,ACCOUNT_TITLE) values ('14','10','xxxx');
Insert into ACCOUNT_TYPES (ACCOUNT_TYPE,ACCOUNT_NUMBER_PRED,ACCOUNT_TITLE) values ('15','10','xxxx');
Insert into ACCOUNT_TYPES (ACCOUNT_TYPE,ACCOUNT_NUMBER_PRED,ACCOUNT_TITLE) values ('16','10','xxxx');
Insert into ACCOUNT_TYPES (ACCOUNT_TYPE,ACCOUNT_NUMBER_PRED,ACCOUNT_TITLE) values ('17','10','xxxx');
Insert into ACCOUNT_TYPES (ACCOUNT_TYPE,ACCOUNT_NUMBER_PRED,ACCOUNT_TITLE) values ('18','10','xxxx');
Insert into ACCOUNT_TYPES (ACCOUNT_TYPE,ACCOUNT_NUMBER_PRED,ACCOUNT_TITLE) values ('19','10','xxxx');
Insert into ACCOUNT_TYPES (ACCOUNT_TYPE,ACCOUNT_NUMBER_PRED,ACCOUNT_TITLE) values ('1A','10','xxxx');

I can run an hierachy query like so

SELECT lpad(' ', (level -1) * 3) || ACCOUNT_CODE  AS ACCOUNT_CODE,
             ACCOUNT_TITLE TITLE,
             ACCOUNT_PRED PRED,
             ACCOUNT_TYPE ATYPE
     FROM    ACCOUNTS
  CONNECT BY PRIOR ACCOUNT_CODE = ACCOUNT_PRED
  START WITH       ACCOUNT_PRED  IS NULL

and another like so

SELECT lpad(' ', (level -1) * 3) ||ACCOUNT_TYPE ,
             ACCOUNT_TITLE,
             ACCOUNT_NUMBER_PRED
     FROM    ACCOUNT_TYPES
  CONNECT BY PRIOR ACCOUNT_TYPE = ACCOUNT_NUMBER_PRED
  START WITH  ACCOUNT_NUMBER_PRED  IS NULL;

Query one would essentially return these values

0001
   0060
0070
 0042
 0056
0110
 0054

I'm trying to get the account type first in the hierarchy so instead I'm trying to produce this outcome.

11
  0001
     0060
13
  0070
     0042
     0056
  0110
     0054

Can anyone help me produce a query that would essentially include the account type as the very first level and then below them the set of accounts that would report under those accounts.

Any help would be greatly appreciated.

Answer 1

Provided that the keys of account and accounts types are disjunctive this query enriches your data set with the top account type level on hierarchy. (In case that the keys of account a types can be same - add some prefix to distinct).

select ACCOUNT_CODE, nvl(ACCOUNT_PRED,ACCOUNT_TYPE) ACCOUNT_PRED,  ACCOUNT_TITLE from   ACCOUNTS
union all
select ACCOUNT_TYPE ACCOUNT_CODE, null ACCOUNT_PRED, ACCOUNT_TITLE from   ACCOUNT_TYPES where  ACCOUNT_TYPE in (
  select ACCOUNT_TYPE from   ACCOUNTS where ACCOUNT_PRED is NULL)

Note that the upper part simple switches from top level (null) to the corresponding account type using NVL. The second part adds the missing account type level.

Using this as a source simply apply your hierarchical query.

with acc as (     
select ACCOUNT_CODE, nvl(ACCOUNT_PRED,ACCOUNT_TYPE) ACCOUNT_PRED,  ACCOUNT_TITLE from   ACCOUNTS
union all
select ACCOUNT_TYPE ACCOUNT_CODE, null ACCOUNT_PRED, ACCOUNT_TITLE from   ACCOUNT_TYPES where  ACCOUNT_TYPE in (
  select ACCOUNT_TYPE from   ACCOUNTS where ACCOUNT_PRED is NULL)
)
SELECT lpad(' ', (level -1) * 3) || ACCOUNT_CODE  AS ACCOUNT_CODE, 
             ACCOUNT_TITLE TITLE,
             ACCOUNT_PRED PRED 
     FROM    ACC 
  CONNECT BY PRIOR ACCOUNT_CODE = ACCOUNT_PRED
  START WITH     ACCOUNT_PRED  IS NULL;

Which produce result as expected:

ACCOUNT_CODE    TITLE  PRED 
--------------- ------ ------
11              xxxx          
   0001         xxxx   11     
      0060      XXXX   0001   
13              xxxx          
   0070         xxxx   13     
      0042      xxxx   0070   
      0056      xxxx   0070   
   0110         xxxx   13     
      0054      xxxx   0110