Блог

У меня есть две отдельные таблицы oracle, и у каждой есть иерархия. Они связаны ключом ACCOUNT_TYPE . Определение таблицы 1 как таковое.

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

Определение таблицы 2 как таковое

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

Таблица 1 содержит следующие данные

 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');
  

вторая таблица содержит следующие данные

 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');
  

Я могу выполнить иерархический запрос следующим образом

 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
  

и еще один такой

 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;
  

Запрос one по существу вернул бы эти значения

 0001
   0060
0070
 0042
 0056
0110
 0054
  

Я пытаюсь получить тип учетной записи первым в иерархии
, поэтому вместо этого я пытаюсь получить этот результат.

 11
  0001
     0060
13
  0070
     0042
     0056
  0110
     0054
  

Может кто-нибудь помочь мне создать запрос, который по существу включал бы тип учетной записи в качестве самого первого уровня, а затем под ними набор учетных записей, которые будут отчитываться по этим учетным записям.

Любая помощь будет с благодарностью.

При условии, что ключи типов account и accounts являются дизъюнктивными, этот запрос обогащает ваш набор данных верхним уровнем типа account в иерархии. (В случае, если ключи типов учетных записей a могут быть одинаковыми — добавьте некоторый префикс к 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)
  

Обратите внимание, что верхняя часть просто переключается с верхнего уровня (null) на соответствующий тип учетной записи с использованием NVL. Вторая часть добавляет недостающий уровень типа учетной записи.

Используя это в качестве источника, просто примените свой иерархический запрос.

 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;
  

Которые дают ожидаемый результат:

 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
  

Создайте запрос, который предоставит вам необходимые данные для иерархии:

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

это даст вам acount «таблица» (dataset), где ваш pred может быть типом acount. теперь вы можете объединить две таблицы и создать иерархию в результате:

 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
  

честно говоря, я не запускал его, поэтому я не уверен, работает ли он, но идея должна быть в порядке. надеюсь, это поможет.