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

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

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.

Removed MySQL tag, since you are using `CONNECT WITH` which Oracle supports. — Tim Biegeleisen, Oct 02 '16 at 07:37

score 2 · Accepted Answer · answered Oct 02 '16 at 14:09

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

Thank you Marmite that is absolutely awesome! I really appreciate your response. — Miguel, Oct 02 '16 at 14:32

score 0 · Answer 2 · answered Oct 02 '16 at 07:59

Make a query that will preduce you the needed data for the hierarchy:

select ACCOUNT_CODE, nvl(ac.ACCOUNT_PRED, ac.ACCOUNT_TYPE)
from ACCOUNT_TYPES at
join ACCOUNTS ac on (at.ACCOUNT_TYPE = ac.ACCOUNT_TYPE)

this will give you an acount "table" (dataset) where your pred may be an acount type. now you can have a union between the two tables and making a hierarchy on the result:

select *
from 
 (SELECT ACCOUNT,PRED
 FROM    ACCOUNT_TYPES

 union all

 select ACCOUNT, nvl(ac.ACCOUNT_PRED, ac.ACCOUNT_TYPE) as PRED
 from ACCOUNT_TYPES at
 join ACCOUNTS ac on (at.ACCOUNT_TYPE = ac.ACCOUNT_TYPE))
CONNECT BY PRIOR ACCOUNT = PRED
START WITH  PRED  IS NULL

to be honest i havent run it so i am not sure if it works, but the idea should be ok. hope it helps.

Sql Hierarchy query with two tables

2 Answers2