Задача. Склейка запроса о максимумах числовых полей.

По всем таблицам текущей схемы, где есть поля типа NUMBER составить отчет следующего вида:

Итак, нужно динамически склеить запрос следующего вида:

select 'KK_DEPOSIT' as TABLE_NAME,

'DEPOSIT_ID:' || nvl(to_char(max(DEPOSIT_ID)), 'null') || ';' ||

'AMOUNT:' || nvl(to_char(max(AMOUNT)), 'null') as COLUMNS_AND_MAXVALUES,

nvl(to_char(max(DEPOSIT_ID)), '0') || '+' ||

nvl(to_char(max(AMOUNT)), '0') as MAXVALUES_SEPARATED_BY_PLUSES,

nvl(max(DEPOSIT_ID), 0) + nvl(max(AMOUNT), 0) as SUM_MAXVALUES

from KK_DEPOSIT

union all

select 'KK_RATE' as TABLE_NAME,

'CURRENCY_CODE:' || nvl(to_char(max(CURRENCY_CODE)), 'null') || ';' ||

'RATE:' || nvl(to_char(max(RATE)), 'null') as COLUMNS_AND_MAXVALUES,

nvl(to_char(max(CURRENCY_CODE)), '0') || '+' ||

nvl(to_char(max(RATE)), '0') as MAXVALUES_SEPARATED_BY_PLUSES,

nvl(max(CURRENCY_CODE), 0) + nvl(max(RATE), 0) as SUM_MAXVALUES

from KK_RATE;

Опишем нижеприведенный запрос, решающий задачу.

В подзапросе q1 соберем информацию об имеющихся в текущей схеме полях типа NUMBER, а также конструкции по поиску максимальных значений, которые принимают эти поля.

Затем подставим q1 в запрос для склейки нескольких строк с группировкой. Будем создавать сразу три склеенных поля для каждой таблицы - column_and_max_col_value_list, to_char_max_col_value_0_list, max_col_value_0_list. Результат склейки назовем q2.

В итоговом запросе соберем select-ы по запросу требуемых значений из таблиц и соединим их конструкциями 'union all'.

Итоговый запрос выдает список select-ов, который можно скопировать, выполнить и получить результат.

Стандартный запрос по склейке имеет ограничение в 4000 символов на длину склееной строки, поэтому вполне вероятно, что получившиеся select-ы по суммарной длине превзойдут 4000 символов, поэтому подставлять итоговый запрос в стандартный запрос по склейке не будем.

select decode(rownum, 1, '', 'union all ') || 'select ''' || table_name ||

''' as TABLE_NAME, ' || column_and_max_col_value_list ||

' as COLUMNS_AND_MAXVALUES, ' || to_char_max_col_value_0_list ||

' as MAXVALUES_SEPARATED_BY_PLUSES, ' || max_col_value_0_list ||

' as SUM_MAXVALUES from ' || table_name as sql_statement

from (select table_name,

max(column_and_max_col_value_list) keep(dense_rank last order by lvl) as column_and_max_col_value_list,

max(to_char_max_col_value_0_list) keep(dense_rank last order by lvl) as to_char_max_col_value_0_list,

max(max_col_value_0_list) keep(dense_rank last order by lvl) as max_col_value_0_list

from (select table_name,

substr(sys_connect_by_path('''' || column_name ||

':''||' ||

max_col_value_null,

'||'';''||'),

8) as column_and_max_col_value_list,

substr(sys_connect_by_path(to_char_max_col_value_0,

'||''+''||'),

8) as to_char_max_col_value_0_list,

substr(sys_connect_by_path(max_col_value_0, '+'), 2) as max_col_value_0_list,

level lvl

from (select tc.table_name,

tc.column_id,

tc.column_name,

lag(tc.column_name) over(partition by tc.table_name order by tc.column_id) as prev_column_name,

'nvl(max(' || tc.column_name || '),0)' as max_col_value_0,

'nvl(to_char(max(' || tc.column_name ||

')),''0'')' as to_char_max_col_value_0,

'nvl(to_char(max(' || tc.column_name ||

')),''null'')' as max_col_value_null

from user_tab_columns tc, user_objects o

where tc.data_type = 'NUMBER'

and tc.table_name = o.object_name

and o.object_type = 'TABLE') q1

start with prev_column_name is null

connect by table_name = prior table_name

and prev_column_name = prior column_name)

group by table_name) q2;

Предлагается еще оин вариант решения – через WM_CONCAT (без сортировки по column_id):

select decode(rownum, 1, '', 'union all ') || str

from (select 'select ''' || tc.table_name || ''' as TABLE_NAME,' ||

replace(wm_concat('''' || tc.column_name ||

':''|| nvl(to_char(max(' || tc.column_name ||

')),''null'')') ||

' as COLUMNS_AND_MAXVALUES',

'''),',

''')||'';''||') || ',' ||

replace(wm_concat('nvl(to_char(max(' || tc.column_name ||

')),''0'')') ||

' as MAXVALUES_SEPARATED_BY_PLUSES',

'''),',

''')||''+''||') || ',' ||

substr(replace(wm_concat('+nvl(max(' || tc.column_name ||

'),0)') || ' as SUM_MAXVALUES ',

'0),',

'0)'),

2) || 'from ' || tc.table_name as str

from user_tab_columns tc

inner join user_tables t

on tc.table_name = t.table_name

where tc.data_type = 'NUMBER'

group by tc.table_name);