Управление нулевыми значениями xmlagg
У меня есть запрос следующей структуры:
SELECT 'SELECT ' || col_list || ' from schema.table;' from( Select table_name, rtrim(xmlagg(xml element(e, Case when datatype in ('blob', 'timestamp') then null else column_name end , ', ').extract('//text()') order by c_id).getclobval(), ', ' ) col_list from all_tab_cols where schema ='schema' and table in ('t1', 't2') group by table_name)
Это дает мне вывод, когда первые два, четвертый и шестой столбцы имеют тип blob или timestamp:
SELECT , , third_col, , fifth_col, from schema.table;
Как изменить запрос, чтобы он предоставлял:
SELECT third_col, fifth_col from schema.table;
Этот результирующий оператор select далее сохраняется в виде строки в фрейме данных spark (я использую Scala). Итак, если мы можем изменить запрос с помощью регулярного выражения или замены подстрок, это также может сработать, и я тоже открыт для этих предложений, но я был бы признателен, если бы мне не пришлось взламывать его таким образом и управлять им. на самой стороне запроса.
Ответы 1
Я думаю, вы слишком усложняете это; просто не включайте эти столбцы вообще, отфильтровав их в предложении where:
...
from all_tab_cols
where owner = 'schema'
and table_name in ('t1', 't2')
and data_type != 'BLOB'
and data_type not like 'TIMESTAMP%'
group by table_name
)
Затем вы можете удалить выражение case.
Вы также можете немного упростить его:
select 'SELECT '
|| rtrim(xmlagg(xmlelement(e, column_name, ', ').extract('//text()') order by column_id).getclobval(), ', ' )
|| ' FROM ' || owner || '.' || table_name || ';'
from all_tab_cols
where owner = 'schema'
and table_name in ('t1', 't2')
and data_type != 'BLOB'
and data_type not like 'TIMESTAMP%'
group by owner, table_name;
Позвольте мне обновить запрос о том, как это на самом деле выглядит. С listagg, когда мы используем регистр и если он равен нулю, агрегированный столбец просто игнорирует его, но это не относится к xmlagg. Вот почему я ищу лучший способ справиться с этим, чтобы из оператора select исключались только определенные типы столбцов.
Я не понимаю вашего второго пункта. Он исключает только столбцы с этими типами, а не любые другие столбцы для этой таблицы или всей таблицы.
Не могли бы вы взглянуть на первый. обновленный запрос. Он пытается получить имена таблиц и их соответствующие объединенные имена столбцов во вложенном выборе. Итак, если t1 имеет три столбца типа varchar, number и blob, вложенный запрос должен вернуть t1 с col_list как c1, c2. Если вы поместите предложение where, в котором говорится, что тип данных не в ('blob'), он вообще не вернет это имя_таблицы и список_столбцов. Если я ошибаюсь, напишите пример запроса и продемонстрируйте его.
Ты прав. Это работает, мой плохой. Я предположил, что если я попытаюсь использовать not in внутри предложения where, он исключит целые таблицы, в которых есть столбцы исключенного типа. Спасибо
Хорошо. IN на самом деле не работает с метками времени, поскольку они имеют размеры как часть самого типа данных, поэтому я немного изменил это. Я также добавил упрощенный запрос и демонстрацию.
Я не могу использовать listagg, потому что агрегированный список столбцов превышает 4000 байт, во-вторых, я не могу использовать предложение where, потому что вместе со списком я получаю имена таблиц. Я только что предоставил вложенный выбор агрегированного списка столбцов, но есть несколько таблиц, и для каждой таблицы мы создадим свой конкатенированный список. Итак, если мы используем предложение where, оно исключит весь этот агрегированный список столбцов для этой таблицы, а не только те конкретные столбцы типа blob и timestamp.