Miguel Miguel - 2 months ago 6
SQL Question

Sql Hierarchy query with two tables

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

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