Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 запрос на словарь данных  [new]
liloracle
Guest
Здравствуйте.
Хочу попросить помощи у людей кто знает.
Помогите пожалуйста допилить запрос.

В чем суть...
Надо вывести подобную штуку...
ИмяГлавнойТаблицы СтолбцыПервичногоКлюча ПодчиненныеТаблицы

Данные в последних двух столбцах должны быть выведены подряд, через разделитель например через * без использования функций wm_concat и listagg...

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

with t as
(
select s.table_name maintable,
(select c.column_name from user_cons_columns c where c.constraint_name = s.constraint_name and c.table_name = s.table_name) pk1, f.table_name reftable
from user_constraints f join user_constraints s on (f.r_constraint_name = s.constraint_name)
)
select distinct maintable, listagg(pk1,',') within group (order by maintable) over (partition by pk1) pkk1, listagg(reftable, ', ') within group (order by reftable) over (partition by maintable,pk1) aa
from t
order by 1;
5 май 13, 21:47    [14260489]     Ответить | Цитировать Сообщить модератору
 Re: запрос на словарь данных  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10040
with t as (
           select  s.table_name parent_table,
                   s.constraint_name,
                   listagg(column_name,',') within group (order by position) parent_table_pk_column_list
            from      user_constraints s
                   join
                      user_cons_columns c
                     on (
                             s.constraint_type = 'P'
                         and
                             c.constraint_name = s.constraint_name
                        )
            group by s.table_name,
                     s.constraint_name
          )
select  parent_table,
        parent_table_pk_column_list,
        listagg(f.table_name,',') within group (order by f.table_name) child_table_list
  from      t
        left join
            user_constraints f
          on (
              f.r_constraint_name = t.constraint_name
             )
  group by parent_table,
           parent_table_pk_column_list
  order by parent_table
/


Например:

SQL> connect hr
Enter password:
Connected.
SQL> with t as (
  2             select  s.table_name parent_table,
  3                     s.constraint_name,
  4                     listagg(column_name,',') within group (order by position) parent_table_pk_column_list
  5              from      user_constraints s
  6                     join
  7                        user_cons_columns c
  8                       on (
  9                               s.constraint_type = 'P'
 10                           and
 11                               c.constraint_name = s.constraint_name
 12                          )
 13              group by s.table_name,
 14                       s.constraint_name
 15            )
 16  select  parent_table,
 17          parent_table_pk_column_list,
 18          listagg(f.table_name,',') within group (order by f.table_name) child_table_list
 19    from      t
 20          left join
 21              user_constraints f
 22            on (
 23                f.r_constraint_name = t.constraint_name
 24               )
 25    group by parent_table,
 26             parent_table_pk_column_list
 27    order by parent_table
 28  /

PARENT_TABLE                   PARENT_TABLE_PK_COLUMN_LIST    CHILD_TABLE_LIST
------------------------------ ------------------------------ ----------------------------------------
COUNTRIES                      COUNTRY_ID                     LOCATIONS
DEPARTMENTS                    DEPARTMENT_ID                  EMPLOYEES,JOB_HISTORY
EMPLOYEES                      EMPLOYEE_ID                    DEPARTMENTS,EMPLOYEES,JOB_HISTORY
JOBS                           JOB_ID                         EMPLOYEES,JOB_HISTORY
JOB_HISTORY                    EMPLOYEE_ID,START_DATE
LOCATIONS                      LOCATION_ID                    DEPARTMENTS
REGIONS                        REGION_ID                      COUNTRIES

7 rows selected.

SQL>


SY.
5 май 13, 23:18    [14260703]     Ответить | Цитировать Сообщить модератору
 Re: запрос на словарь данных  [new]
liloracle
Guest
SY,

благодарю за помощь мне.

проблема в том что надо его сделать совсем без функции listagg, wm_concat что добавляет трудностей.
с listagg допилил сам... похожим образом...
5 май 13, 23:45    [14260768]     Ответить | Цитировать Сообщить модератору
 Re: запрос на словарь данных  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10040
liloracle
проблема в том что надо его сделать совсем без функции listagg, wm_concat что добавляет трудностей.


XQUERY пойдет?

with t as (
           select  s.table_name parent_table,
                   s.constraint_name,
                   rtrim(xmlagg(xmlelement(c,column_name,',').extract('//text()') order by position),',') parent_table_pk_column_list
            from      user_constraints s
                   join
                      user_cons_columns c
                     on (
                             s.constraint_type = 'P'
                         and
                             c.constraint_name = s.constraint_name
                        )
            group by s.table_name,
                     s.constraint_name
          )
select  parent_table,
        parent_table_pk_column_list,
        rtrim(xmlagg(xmlelement(c,f.table_name,',').extract('//text()') order by f.table_name),',') child_table_list
  from      t
        left join
            user_constraints f
          on (
              f.r_constraint_name = t.constraint_name
             )
  group by parent_table,
           parent_table_pk_column_list
  order by parent_table
/

PARENT_TABLE                   PARENT_TABLE_PK_COLUMN_LIST    CHILD_TABLE_LIST
------------------------------ ------------------------------ ----------------------------------------
COUNTRIES                      COUNTRY_ID                     LOCATIONS
DEPARTMENTS                    DEPARTMENT_ID                  EMPLOYEES,JOB_HISTORY
EMPLOYEES                      EMPLOYEE_ID                    DEPARTMENTS,EMPLOYEES,JOB_HISTORY
JOBS                           JOB_ID                         EMPLOYEES,JOB_HISTORY
JOB_HISTORY                    EMPLOYEE_ID,START_DATE
LOCATIONS                      LOCATION_ID                    DEPARTMENTS
REGIONS                        REGION_ID                      COUNTRIES

7 rows selected.

SQL>


Ну а если нет, то иерархия:

with t1 as (
            select  s.table_name parent_table,
                    s.constraint_name,
                    ltrim(sys_connect_by_path(column_name,','),',') parent_table_pk_column_list
             from      user_constraints s
                    join
                       user_cons_columns c
                      on (
                              s.constraint_type = 'P'
                          and
                              c.constraint_name = s.constraint_name
                         )
             where connect_by_isleaf = 1
             start with position = 1
             connect by s.table_name = prior s.table_name
                    and position = prior position + 1
           ),
     t2 as (
            select  parent_table,
                    parent_table_pk_column_list,
                    f.table_name child_table,
                    row_number() over(partition by parent_table order by f.table_name) rn
              from      t1
                    left join
                        user_constraints f
                      on (
                          f.r_constraint_name = t1.constraint_name
                         )
           )
select  parent_table,
        parent_table_pk_column_list,
        ltrim(sys_connect_by_path(child_table,','),',') child_table_list
  from  t2
  where connect_by_isleaf = 1
  start with rn = 1
  connect by parent_table = prior parent_table
         and rn = prior rn + 1
  order by parent_table
/

PARENT_TABLE                   PARENT_TABLE_PK_COLUMN_LIST    CHILD_TABLE_LIST
------------------------------ ------------------------------ ----------------------------------------
COUNTRIES                      COUNTRY_ID                     LOCATIONS
DEPARTMENTS                    DEPARTMENT_ID                  EMPLOYEES,JOB_HISTORY
EMPLOYEES                      EMPLOYEE_ID                    DEPARTMENTS,EMPLOYEES,JOB_HISTORY
JOBS                           JOB_ID                         EMPLOYEES,JOB_HISTORY
JOB_HISTORY                    EMPLOYEE_ID,START_DATE
LOCATIONS                      LOCATION_ID                    DEPARTMENTS
REGIONS                        REGION_ID                      COUNTRIES

7 rows selected.

SQL>


SY.

Сообщение было отредактировано: 6 май 13, 01:25
6 май 13, 01:24    [14260958]     Ответить | Цитировать Сообщить модератору
 Re: запрос на словарь данных  [new]
liloracle
Guest
SY
liloracle
проблема в том что надо его сделать совсем без функции listagg, wm_concat что добавляет трудностей.


XQUERY пойдет?

with t as (
           select  s.table_name parent_table,
                   s.constraint_name,
                   rtrim(xmlagg(xmlelement(c,column_name,',').extract('//text()') order by position),',') parent_table_pk_column_list
            from      user_constraints s
                   join
                      user_cons_columns c
                     on (
                             s.constraint_type = 'P'
                         and
                             c.constraint_name = s.constraint_name
                        )
            group by s.table_name,
                     s.constraint_name
          )
select  parent_table,
        parent_table_pk_column_list,
        rtrim(xmlagg(xmlelement(c,f.table_name,',').extract('//text()') order by f.table_name),',') child_table_list
  from      t
        left join
            user_constraints f
          on (
              f.r_constraint_name = t.constraint_name
             )
  group by parent_table,
           parent_table_pk_column_list
  order by parent_table
/

PARENT_TABLE                   PARENT_TABLE_PK_COLUMN_LIST    CHILD_TABLE_LIST
------------------------------ ------------------------------ ----------------------------------------
COUNTRIES                      COUNTRY_ID                     LOCATIONS
DEPARTMENTS                    DEPARTMENT_ID                  EMPLOYEES,JOB_HISTORY
EMPLOYEES                      EMPLOYEE_ID                    DEPARTMENTS,EMPLOYEES,JOB_HISTORY
JOBS                           JOB_ID                         EMPLOYEES,JOB_HISTORY
JOB_HISTORY                    EMPLOYEE_ID,START_DATE
LOCATIONS                      LOCATION_ID                    DEPARTMENTS
REGIONS                        REGION_ID                      COUNTRIES

7 rows selected.

SQL>


Ну а если нет, то иерархия:

with t1 as (
            select  s.table_name parent_table,
                    s.constraint_name,
                    ltrim(sys_connect_by_path(column_name,','),',') parent_table_pk_column_list
             from      user_constraints s
                    join
                       user_cons_columns c
                      on (
                              s.constraint_type = 'P'
                          and
                              c.constraint_name = s.constraint_name
                         )
             where connect_by_isleaf = 1
             start with position = 1
             connect by s.table_name = prior s.table_name
                    and position = prior position + 1
           ),
     t2 as (
            select  parent_table,
                    parent_table_pk_column_list,
                    f.table_name child_table,
                    row_number() over(partition by parent_table order by f.table_name) rn
              from      t1
                    left join
                        user_constraints f
                      on (
                          f.r_constraint_name = t1.constraint_name
                         )
           )
select  parent_table,
        parent_table_pk_column_list,
        ltrim(sys_connect_by_path(child_table,','),',') child_table_list
  from  t2
  where connect_by_isleaf = 1
  start with rn = 1
  connect by parent_table = prior parent_table
         and rn = prior rn + 1
  order by parent_table
/

PARENT_TABLE                   PARENT_TABLE_PK_COLUMN_LIST    CHILD_TABLE_LIST
------------------------------ ------------------------------ ----------------------------------------
COUNTRIES                      COUNTRY_ID                     LOCATIONS
DEPARTMENTS                    DEPARTMENT_ID                  EMPLOYEES,JOB_HISTORY
EMPLOYEES                      EMPLOYEE_ID                    DEPARTMENTS,EMPLOYEES,JOB_HISTORY
JOBS                           JOB_ID                         EMPLOYEES,JOB_HISTORY
JOB_HISTORY                    EMPLOYEE_ID,START_DATE
LOCATIONS                      LOCATION_ID                    DEPARTMENTS
REGIONS                        REGION_ID                      COUNTRIES

7 rows selected.

SQL>


SY.


Тысячу раз благодарен.
С иерархией отличный вариант. Заодно разберусь с функцией sysconnectbypath
6 май 13, 07:03    [14261091]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить