Oracle найти таблицу по полю
LISTAGG упорядочивает данные, объединенные в группы конструкцией ORDER BY, затем соединяет указаный столбец measure_expr.
1) Как одиночная агрегатная функция, LISTAGG обрабатывает все строки и возвращает одно значение.
2) Как групповая агрегатная функция, LISTAGG обрабатывает и возвращает данные для каждой группы определенной в GROUP BY.
3) Как аналитическая функция, LISTAGG обрабатывает данные разбитые на блоки, задаваемые одним или несколькими выражениями query_partition_clause.
Пример агрегатной функции
Пример аналитической функции
17 Answers 17
I've tried using this statement below to find an appropriate column based on what I think it should be named but it returned no results.*
A column isn't an object. If you mean that you expect the column name to be like '%DTN%', the query you want is:
But if the 'DTN' string is just a guess on your part, that probably won't help.
By the way, how certain are you that '1/22/2008P09RR8' is a value selected directly from a single column? If you don't know at all where it is coming from, it could be a concatenation of several columns, or the result of some function, or a value sitting in a nested table object. So you might be on a wild goose chase trying to check every column for that value. Can you not start with whatever client application is displaying this value and try to figure out what query it is using to obtain it?
Anyway, diciu's answer gives one method of generating SQL queries to check every column of every table for the value. You can also do similar stuff entirely in one SQL session using a PL/SQL block and dynamic SQL. Here's some hastily-written code for that:
There are some ways you could make it more efficient too.
In this case, given the value you are looking for, you can clearly eliminate any column that is of NUMBER or DATE type, which would reduce the number of queries. Maybe even restrict it to columns where type is like '%CHAR%'.
Instead of one query per column, you could build one query per table like this:
Как запросить базу данных Oracle, чтобы отобразить имена всех таблиц в ней?
Это предполагает, что у вас есть доступ к представлению DBA_TABLES словаря данных. Если у вас нет этих привилегий, но они нужны, вы можете запросить, чтобы администратор БД явно предоставил вам привилегии для этой таблицы, или чтобы администратор БД предоставил вам SELECT ANY DICTIONARY привилегию или SELECT_CATALOG_ROLE роль (любая из которых позволит вам запросить любую таблицу словаря данных). ). Конечно, вы можете захотеть исключить определенные схемы, такие как SYS и SYSTEM имеющие большое количество таблиц Oracle, которые вам, вероятно, не нужны .
Кроме того, если у вас нет доступа DBA_TABLES , вы можете просмотреть все таблицы, к которым у вашей учетной записи есть доступ, через ALL_TABLES представление:
Хотя это может быть подмножеством таблиц, доступных в базе данных ( ALL_TABLES показывает информацию обо всех таблицах, к которым у вашего пользователя есть доступ).
Если вас интересуют только те таблицы, которыми вы владеете, а не те, к которым у вас есть доступ, вы можете использовать USER_TABLES :
Поскольку он USER_TABLES содержит информацию только о тех таблицах, которыми вы владеете, у него нет OWNER столбца - владельцем по определению является вы.
Oracle также имеет ряд устаревших данных словаря views-- TAB , DICT , TABS и CAT для example-- , которые можно было бы использовать. В целом, я бы не советовал использовать эти устаревшие представления, если вам абсолютно не нужно перенести свои сценарии в Oracle 6. Oracle не изменяла эти представления в течение длительного времени, поэтому у них часто возникают проблемы с объектами более новых типов. Например, TAB и CAT представления, и представления показывают информацию о таблицах, которые находятся в корзине пользователя, в то время как [DBA|ALL|USER]_TABLES все представления отфильтровывают их. CAT также показывает информацию о материализованных журналах представлений с TABLE_TYPE «TABLE», что вряд ли будет тем, что вы действительно хотите. DICT объединяет таблицы и синонимы и не говорит вам, кто владеет объектом.
Можно ли в Oracle искать в каждом поле каждой таблицы определенное значение?
Существуют сотни таблиц с тысячами строк в некоторых таблицах, поэтому я знаю, что запрос может занять очень много времени. Но единственное, что я знаю, это то, что значение поля, которое я хотел бы запросить, равно 1/22/2008P09RR8 .
Я попытался использовать этот оператор ниже, чтобы найти соответствующий столбец на основе того, что, по моему мнению, следует назвать, но он не дал результатов.
По этой базе данных нет абсолютно никакой документации, и я понятия не имею, откуда берется это поле.
@LalitKumarB Указанная вами страница больше не доступна. Можно ли в ответ выложить какую-нибудь информацию?
Я попытался использовать этот оператор ниже, чтобы найти соответствующий столбец на основе того, что, по моему мнению, следует назвать, но он не дал результатов. *
Столбец - это не объект. Если вы имеете в виду, что ожидаете, что имя столбца будет иметь вид "% DTN%", то вам нужен следующий запрос:
Но если строка 'DTN' - это всего лишь предположение с вашей стороны, это, вероятно, не поможет.
Кстати, насколько вы уверены, что «1/22 / 2008P09RR8» - это значение, выбранное непосредственно из одного столбца? Если вы совсем не знаете, откуда он исходит, это может быть объединение нескольких столбцов, или результат какой-либо функции, или значение, находящееся во вложенном табличном объекте. Так что вы можете быть на охоте, пытаясь проверить каждый столбец на это значение. Разве вы не можете начать с любого клиентского приложения, отображающего это значение, и попытаться выяснить, какой запрос он использует для его получения?
В любом случае, ответ diciu дает один метод генерации SQL-запросов для проверки значения каждого столбца каждой таблицы. Вы также можете делать аналогичные вещи полностью в одном сеансе SQL, используя блок PL / SQL и динамический SQL. Вот для этого наспех написанный код:
Есть несколько способов сделать его более эффективным.
В этом случае, учитывая искомое значение, вы можете однозначно исключить любой столбец, имеющий тип NUMBER или DATE, что уменьшит количество запросов. Может быть, даже ограничить его столбцами, где тип подобен "% CHAR%".
Вместо одного запроса на столбец вы можете создать один запрос на таблицу следующим образом:
Чтобы понять рекурсию, сначала надо понять рекурсию. Возможно, поэтому рекурсивные запросы применяют так редко. Наверняка вы представляете что такое SQL-запрос, я расскажу, чем рекурсивные запросы отличаются от обычных. Тема получилась объемная, приготовьтесь к долгому чтению. В основном речь пойдет об Oracle, но упоминаются и другие СУБД.
Суть проблемы
Большинство современных СУБД (Система Управления Базами Данных) — реляционные, т.е. представляют данные в виде двумерной таблицы, в которой есть строки (записи) и столбцы (поля записей). Но на практике мы часто сталкиваемся с иной организацией данных, а именно иерархической.
Взгляните на список файлов на вашем компьютере: все они организованы в виде дерева. Аналогично можно представить книги в библиотеке: Библиотека->Зал->Шкаф->Полка->Книга. То же самое и статьи на сайте: Сайт->Раздел->Подраздел->Статья. Примеры можно приводить долго. Впрочем, тут еще можно разделить все на отдельные таблицы: таблица для хранения списка библиотек, другая таблица для списка залов, третья для шкафов и т.д. Но если заранее не известна глубина вложенности или эта вложенность может меняться, тут уж от иерархии никак не отмашешься.
Проблема в том, что данные, имеющие иерархическую структуру, очень плохо представляются в реляционной модели. В стандарте SQL-92 нет средств для их обработки.
Зато такие средства появились в стандарте SQL-1999. Правда к тому времени в Oracle уже был собственный оператор CONNECT BY. Несмотря на это, в SQL-1999 синтаксис рекурсивных запросов совершенно не похож на синтаксис CONNECT BY в Oracle и использует ключевое слово WITH. Реализация же рекурсивных запросов в других СУБД несколько запоздала, так в MS SQL Server она появилась лишь в версии 2005.
Так же как и в синтаксисе, есть отличия и в терминологии. В Oracle обычно обсуждаемые запросы называются “иерархические”, у всех остальных “рекурсивные”. Суть от этого не меняется, я буду использовать и то и другое.
От слов к делу!
Для демонстрации будем использовать структуру каталогов, нам потребуется тестовая таблица, состоящая из 3-х полей:
id – идентификатор,
pid – идентификатор родителя (ссылается на id другой записи в той же таблице),
title – название каталога (вместо него может быть что угодно, даже несколько полей или ссылок к другим таблицам).
Здесь я использовал синтаксис mySQL, в других СУБД он несколько отличается. Так, в Oracle используются другие типы данных: вместо int — number, а вместо varchar — varchar2.
Заполнить табличку тестовыми данными не составит труда. Предлагаю записать список серверов, расположенных на территории различных предприятий в разных городах. Таким образом, в единой таблице оказываются и страны, и города, и фирмы, и сервера.
Думаю, остальные строки заполнить не составит сложностей. Получается почти как на картинке, только не в том порядке. Я специально заполнял не по-порядку, чтобы простым SELECT * FROM test_table получалось не иерархическая структура:
Тестовые данные готовы. Приступим к выборкам.
mySQL
Вынужден огорчить пользователей mySQL – в этой СУБД придется рекурсию организовывать внешними по отношению к СУБД средствами, например на php (код приводить не буду, его публиковали не раз, например здесь, достаточно поискать по запросу “mySQL tree” и т.п.).
Впрочем, есть иной подход, заключающийся в создании левой и правой границы для каждого узла, предложенный Джо Селко. Тогда можно будет обойтись обычными, не рекурсивными запросами.
Как-то я выводил дерево объектов в действующем проекте на php. База данных была на mySQL. Поплевавшись на отсутствие удобных операторов, я решил тогда не отображать все дерево целиком, а показать пользователю только первый уровень (схлопнутое дерево). При клике по плюсику в узле дерева отображались дочерние узлы для выбранного объекта, при этом они подгружались через AJAX. Выборка дочерних узлов по известному pid происходит быстро, поэтому интерфейс получился вполне шустрым. Возможно, это не лучшее решение, но оно имеет право на жизнь.
SQL-1999
В отличие от предыдущего стандарта SQL-92, в названии следующего решили отобразить номер тысячелетия, чтобы он не страдал от проблемы двухтысячного года. Помимо этого :-), появились новые типы данных (LOB), новые предикаты SIMILAR и DISTINCT, точки сохранения транзакций, объекты и их методы, и многое другое. Среди нововведений появились также рекурсивные запросы, о которых мы сейчас и поговорим.
Для получения иерархических данных используется временное представление, которое описывается оператором WITH. После этого из нее выбираются данные простым селектом. В общем виде синтаксис примерно такой:
В MS SQL нет ключевого слова RECURSIVE, его следует опустить. Но в остальном все то же самое. Такой синтаксис поддерживается в DB2, Sybase iAnywhere, MS SQL, начиная с версии 2005, и во всех базах данных, которые поддерживают стандарт SQL 1999.
Тогда для получения нашего дерева запрос получится такой:
WITH RECURSIVE
Rec (id, pid, title)
AS (
SELECT id, pid, title FROM test_table
UNION ALL
SELECT Rec.id, Rec.pid, Rec.title
FROM Rec, test_table
WHERE Rec.id = test_table.pid
)
SELECT * FROM Rec
WHERE pid is null ;
Здесь используется рекурсивная таблица Rec, которую мы сами придумали, построив ее по исходной таблице test_table. В описании Rec указано правило, каким образом соединять: WHERE Rec.id = test_table.pid. А в главном запросе отметили, что начинать надо с записи, у которой pid является пустым, т.е. с корневой записи.
Честно говоря, я никогда не работал в MS SQL Server 2005 или другой СУБД, использующей такой синтаксис. Поэтому написал этот запрос чисто из теоретических соображений. Для общности картины, чтобы было с чем сравнить.
В MS SQL 2008 можно применить более новое средство hierarchyid. Спасибо XaocCPS за его описание.
Oracle
Описание синтаксиса в документации напоминает бусы: на единую нить запроса нанизываются нужные операторы. Никому не приходило в голову сделать украшение для гички?
Тут видно, что единственно важное условие для построения иерархического запроса – это оператор CONNECT BY, остальное “нанизывается” по мере надобности.
Необязательный оператор START WITH говорит Ораклу с чего начинать цикл, т.е. какая строка (или строки) будет корневой. Условие может быть практически любым, можно даже использовать функции или внутренние запросы: pid is null, или или даже substr(title, 1, 1) = ‘Р’.
Как же получить нормальную иерархию? Нужно использовать специальный оператор, который называется PRIOR. Это обычный унарный оператор, точно такой же как + или -. “Позвоните родителям” – говорит он, заставляя Оракл обратиться к предыдущей записи. С его помощью можно написать правило pid = PRIOR id (или PRIOR как говорится, от перестановки мест…).
Что получается? Оракл находит первую запись, удовлетворяющую условию в START WITH, и принимается искать следующую. При этом к той первой записи можно обратиться через PRIOR. Если мы все сделали правильно, то Оракл будет искать записи, в которых в поле для хранения информации о родителе (pid) будет содержаться значение, равное идентификатору id нашей первой записи. Таким образом будут найдены все потомки корневой записи. А так как процесс рекурсивный, аналогичный поиск будет продолжаться с каждой найденной строкой, пока не отыщутся все потомки.
Теперь у нас есть все необходимое, чтобы написать иерархический запрос в Oracle. Но прежде чем мы его напишем, расскажу еще об одной штуке. Порядок строк это хорошо, но нам было бы трудно понять, две строки рядом это родитель и его потомок или два брата-потомка одного родителя. Пришлось бы сверять id и pid. К счастью, Oracle предлагает в помощь дополнительный псевдостолбец LEVEL. Как легко догадаться, в нем записывается уровень записи по отношению к корневой. Так, 1-ая запись будет иметь уровень 1, ее потомки уровень 2, потомки потомков — 3 и т.д.
Неплохо. Все дочерние строки оказываются под своими родителями. Сортировку бы еще добавить, чтобы записи одного уровня выводились не абы-как, а по алфавиту. Ну чтож, сортировка это просто: добавим в конец запроса конструкцию ORDER BY title.
SELECT level , id, pid, title
FROM test_table
START WITH pid is null
CONNECT BY PRIOR > ORDER BY title;
О, нет! Вся иерархия поломалась. Что же получилось? Оракл честно выбрал нужные строки в порядке иерархии (об этом говорит правильная расстановка level), а затем пересортировал их согласно правилу ORDER BY. Чтобы указать Ораклу, что сортировать надо только в пределах одного уровня иерархии, нам поможет маленькая добавка в виде оператора SIBLINGS. Достаточно изменить условие сортировки на ORDER SIBLINGS BY title – и все встанет на свои места.
Кстати, возможно все еще не понятно, почему этот порядок строк является деревом. Можно убрать все “лишние” поля и добавить отступы, станет более наглядно:
SELECT lpad( ' ' , 3* level )||title as Tree
FROM test_table
START WITH pid is null
CONNECT BY PRIOR > ORDER SIBLINGS BY title;
Ну вот, теперь все в точности, как на картинке в самом начале статьи.
Помните, файловые менеджеры обычно пишут путь к каталогу, в котором вы находитесь: /home/maovrn/documents/ и т.п.? Неплохо было бы и нам сделать так же. А сделать это можно абсолютно не напрягаясь: специалисты из Oracle все уже сделали за нас. Просто берем и используем функцию SYS_CONNECT_BY_PATH(). Она принимает два параметра через запятую: название колонки и строку с символом-разделителем. Будем не оригинальны, напишем так: SYS_CONNECT_BY_PATH(title, ‘/’).
Заодно ограничим вывод, выбрав только одну строку. Для этого, как всегда, нужно добавить условие WHERE. Даже в иерархическом запросе ограничивающее условие применяется ко всем строкам. Вставить его надо до иерархической конструкции, сразу после FROM. Для примера определим путь до “Сервер 1”, который у нас записан с > SELECT SYS_CONNECT_BY_PATH(title, '/' ) as Path
FROM test_table
WHERE > START WITH pid is null
CONNECT BY PRIOR >
Еще может быть полезен псевдостолбец CONNECT_BY_ISLEAF. Его можно использовать так же, как LEVEL. В этом псевдостолбце напротив каждой строки проставляется 0 или 1. Если есть потомки – проставится 0. Если потомков нет, такой узел в дереве называется “листом”, тогда и значение в поле CONNECT_BY_ISLEAF будет равно 1.
Устали? Осталось немного, самое страшное уже позади. Раньше мы использовали оператор PRIOR, который ссылался к родительской записи. Помимо него есть другой унарный оператор CONNECT_BY_ROOT, который ссылается (ни за что не догадаетесь!) на корневую запись, т.е. на самую первую в выборке.
SELECT id, pid, title, level ,
CONNECT_BY_ISLEAF as IsLeaf,
PRIOR title as Parent,
CONNECT_BY_ROOT title as Root
FROM test_table
START WITH pid is null
CONNECT BY PRIOR > ORDER SIBLINGS BY title;
Стоит отметить, что если в результате выполнения запроса обнаружится петля, Oracle выдаст ошибку. К счастью, ее можно обойти, хотя если в данных содержатся петли – это явно ошибка, в деревьях не бывает петель. На картинке с “бусами” запроса был нарисован оператор NOCYCLE после CONNECT BY – его мы и будем применять. Теперь запрос не будет вылетать. А чтобы определить “больной” участок, воспользуемся псевдостолбцом CONNECT_BY_ISCYCLE – в нем во всех хороших строках будет записано 0, а в тех, которые приводят к петлям, волшебным образом окажется 1.
Чтобы проиллюстрировать это, придется немного подпортить данные. ЛискиПресс ссылается у нас на город Лиски; изменим запись Лиски, чтобы она ссылалась на ЛискиПресс (не забудьте про commit – я вечно забываю):
Если мы запустим какой-нибудь из предыдущих запросов, увидим, что и Лиски, и ЛискиПресс выпали из выборки, будто их нет совсем. Бегая в цикле, Оракл просто перестал на них натыкаться, т.к. нет пути от записи Россия к городу Лиски. Изменим условия START WITH, чтобы начинать с города Лиски – появится ошибка. Умный Оракл видит что запись уже выбиралась ранее и отказывается бегать в бесконечном цикле. Исправляем ошибку:
SELECT CONNECT_BY_ISCYCLE as cycl, id, pid, title
FROM test_table
START WITH > CONNECT BY NOCYCLE PRIOR >
Практические примеры
Иерархические запросы можно применять не только там, где есть явная иерархия.
Например, рассмотрим задачу получения списка пропущенных номеров из последовательности. Это бывает нужно, когда в некоей таблице id генерируется автоматически путем увеличения на 1, но часть записей были удалены. Нужно получить список удаленных номеров. По хорошей традиции, это следует сделать одним селектом.
Подготовим тестовые данные. Удалим из нашей таблицы пару записей:
С чего начнем? Во-первых, неплохо было бы получить список номеров подряд от 1 до максимального значения в нашей таблице, чтобы было с чем сравнивать. Выяснить максимальное значение id из таблицы, думаю, не составит никаких трудностей:
А вот чтобы сгенерировать последовательность от 1 до max как раз и понадобится рекурсивный запрос. Ведь как здорово просто взять и получить нужное количество строк! Достаточно будет их пронумеровать – и вот список готов.
Конструкция “SELECT … FROM dual” используется, когда надо вычислить значение функции, не производя при этом выборки данных. Dual – это системная таблица, состоящая из одного столбца и одной строки. Запрос из нее всегда возвращает одну строку со значением ‘X’. Благодаря такой умопомрачительной стабильности, эту таблицу удобно использовать в качестве источника строк.
Обычно таблицу, которую нагло используют для получения нужного количества строк, не выбирая сами данные, называют pivot. В качестве такой таблицы может выступать любая большая таблица, в том числе системная. Но использование dual в Oracle является более разумным решением.
Теперь, когда список номеров подряд уже есть, достаточно пройтись по нему и сравнить, есть ли такой номер в проверяемой таблице:
SELECT sq.rn
FROM ( SELECT rownum as rn FROM dual
CONNECT BY level WHERE sq.rn not in ( SELECT id FROM test_table)
ORDER BY rn;
Всё. Ведро начищено до блеска, лошадь вооружена биноклем для остроты зрения. Да не коснется вас ROLLBACK. COMMIT!
Похожие записи:
Жаль, что LISTAGG не позволяет использовать DISTINCT, чтобы убрать дубли перед агрегацией-конкатенацией (как в самописанной STRAGG). Но, несомненный плюс, это возможность сортировки элементов в конкатенированной строке (чего не было в STRAGG).
@Fima
Но всегда есть возможность из полученной строки удалить дубликаты.
Если использовать listagg без сортировки, то регулярное выражение будет сложнее
p.s.: да я ответил сам себе почти через два года, потому что забыл как реализовывал тогда и потратил время на решение.
А можно как-то ограничить список строк для группировки в listagg ? Проблема такая: обрабатывается 100 строк, и общая строка listagg() получается больше 4000 символов. ХОтелось быть ограничить в этом случае число строк 30ью, например. Это реально?
@Алексей
К сожалению, мне такой способ неизвестен. В этом случае надо использовать альтернативные методы.
@Алексей
Можно, например, обрезать финальный список строковой функцией до нужных размеров.
@Ярослав
А пример есть? А то я таких не знаю способов )
@Алексей
Если вас устроит CLOB, то можно не ограничивать. Я извернулся вот таким способом, потому как ограничивать и обрезать было нельзя:
DISTINCT
select id, LISTAGG(name, ‘, ‘) WITHIN GROUP (order by name) as lic_list
from
(
select
distinct ld.obj_id as id, dl.short_name as name
from
dic_license dl,
license_det ld
where
dl.id = ld.dic_license_id
)
group by id
@MErdock
У меня почему то после такой регулярки ничего не просходит со строкой (.
@Fima
А почему нельзя так-
select deptno, LISTAGG(t.ename, ‘, ‘) WITHIN GROUP (order by t.empno) as ename_list
from (select distinct deptno,ename from scott.emp ) t
@Mr.Grizz
а не потому, что уникальный id_obj не позволяет ?
@Алексей
У аналитической функции можно задать окно: Range/Rows
Окно задаётся после «order by»
Пример, listagg (поле_3,’,’) withing group (order by поле_2 rows 9 preceding) over (partition by поле_1)
таким образом должны захватиться текущая строка выборки и 9 предыдущих.
@l___RUS___l
закосячил. Listagg не работает с окном.
Необходимо использовать str_agg
у меня получилось сделать нормально LISTAGG в группировке и без повторений. Фишка в том, что нужно в подселекте сделать основную группировку, а во внешнем селекте сделать группировку с листаг — к этому моменту в подзапросе уже будут сгруппированы\удалены повторения и листагг их пропустит.
Ещё момент с двумя листагг в одном запросе — может потребоваться ещё подселект. для одного листагг — один селект с группировкой.
AlexB :
у меня получилось сделать нормально LISTAGG в группировке и без повторений.
SELECT LISTAGG(APPLICID, ‘,’) WITHIN GROUP (ORDER BY APPLICID) into er_desc FROM RTDM_SAS_LOG rsa
WHERE rsa.EVENT_CODE NOT IN (‘0′,’4′,’2203′,’2205′)
AND ROUND(SYSTIMESTAMP,’HH’) — ROUND(rsa.TIME_STAMP,’HH’) < day;
@Евдоким
Почему вы решили, что тут убираются повторы? Это видимо просто частный случай данных.
1) У меня нет такой таблицы, поэтому вот пример, где видно что повторы остались:
Внутренняя группировка строит колекции и делает агрегаты с групой.
Внешний запрос выполняет Listagg подзапросом к коллекции.
Получаем и distinct и нет ограничений на количество Listagg в одной групировке.
with test_data as
(
select ‘A’ as col1, ‘T_a1’ as col2, ‘123’ as col3 from dual
union select ‘A’, ‘T_a1’, ‘456’ from dual
union select ‘A’, ‘T_a1’, ‘789’ from dual
union select ‘A’, ‘T_a2’, ‘123’ from dual
union select ‘A’, ‘T_a2’, ‘456’ from dual
union select ‘A’, ‘T_a2’, ‘111’ from dual
union select ‘A’, ‘T_a3’, ‘999’ from dual
union select ‘B’, ‘T_a1’, ‘123’ from dual
union select ‘B’, ‘T_b1’, ‘740’ from dual
union select ‘B’, ‘T_b1’, ‘846’ from dual
)
select col1
, (select listagg(column_value, ‘,’) within group (order by column_value desc) from table(collect_col2)) as col2s
, (select listagg(column_value, ‘,’) within group (order by column_value desc) from table(collect_col3)) as col3s
from
(
select col1,
collect(distinct col2) as collect_col2,
collect(distinct col3) as collect_col3
from test_data
group by col1
);
Is it possible to search every field of every table for a particular value in Oracle?
There are hundreds of tables with thousands of rows in some tables so I know this could take a very long time to query. But the only thing I know is that a value for the field I would like to query against is 1/22/2008P09RR8 .
I've tried using this statement below to find an appropriate column based on what I think it should be named but it returned no results.
There is absolutely no documentation on this database and I have no idea where this field is being pulled from.
@LalitKumarB The page you listed is no longer accessible. Would it be possible to post some information as an answer?
Похожие записи:
Жаль, что LISTAGG не позволяет использовать DISTINCT, чтобы убрать дубли перед агрегацией-конкатенацией (как в самописанной STRAGG). Но, несомненный плюс, это возможность сортировки элементов в конкатенированной строке (чего не было в STRAGG).
@Fima
Но всегда есть возможность из полученной строки удалить дубликаты.
Если использовать listagg без сортировки, то регулярное выражение будет сложнее
p.s.: да я ответил сам себе почти через два года, потому что забыл как реализовывал тогда и потратил время на решение.
А можно как-то ограничить список строк для группировки в listagg ? Проблема такая: обрабатывается 100 строк, и общая строка listagg() получается больше 4000 символов. ХОтелось быть ограничить в этом случае число строк 30ью, например. Это реально?
@Алексей
К сожалению, мне такой способ неизвестен. В этом случае надо использовать альтернативные методы.
@Алексей
Можно, например, обрезать финальный список строковой функцией до нужных размеров.
@Ярослав
А пример есть? А то я таких не знаю способов )
@Алексей
Если вас устроит CLOB, то можно не ограничивать. Я извернулся вот таким способом, потому как ограничивать и обрезать было нельзя:
DISTINCT
select id, LISTAGG(name, ‘, ‘) WITHIN GROUP (order by name) as lic_list
from
(
select
distinct ld.obj_id as id, dl.short_name as name
from
dic_license dl,
license_det ld
where
dl.id = ld.dic_license_id
)
group by id
@MErdock
У меня почему то после такой регулярки ничего не просходит со строкой (.
@Fima
А почему нельзя так-
select deptno, LISTAGG(t.ename, ‘, ‘) WITHIN GROUP (order by t.empno) as ename_list
from (select distinct deptno,ename from scott.emp ) t
@Mr.Grizz
а не потому, что уникальный id_obj не позволяет ?
@Алексей
У аналитической функции можно задать окно: Range/Rows
Окно задаётся после «order by»
Пример, listagg (поле_3,’,’) withing group (order by поле_2 rows 9 preceding) over (partition by поле_1)
таким образом должны захватиться текущая строка выборки и 9 предыдущих.
@l___RUS___l
закосячил. Listagg не работает с окном.
Необходимо использовать str_agg
у меня получилось сделать нормально LISTAGG в группировке и без повторений. Фишка в том, что нужно в подселекте сделать основную группировку, а во внешнем селекте сделать группировку с листаг — к этому моменту в подзапросе уже будут сгруппированы\удалены повторения и листагг их пропустит.
Ещё момент с двумя листагг в одном запросе — может потребоваться ещё подселект. для одного листагг — один селект с группировкой.
AlexB :
у меня получилось сделать нормально LISTAGG в группировке и без повторений.
SELECT LISTAGG(APPLICID, ‘,’) WITHIN GROUP (ORDER BY APPLICID) into er_desc FROM RTDM_SAS_LOG rsa
WHERE rsa.EVENT_CODE NOT IN (‘0′,’4′,’2203′,’2205′)
AND ROUND(SYSTIMESTAMP,’HH’) — ROUND(rsa.TIME_STAMP,’HH’) < day;
@Евдоким
Почему вы решили, что тут убираются повторы? Это видимо просто частный случай данных.
1) У меня нет такой таблицы, поэтому вот пример, где видно что повторы остались:
Внутренняя группировка строит колекции и делает агрегаты с групой.
Внешний запрос выполняет Listagg подзапросом к коллекции.
Получаем и distinct и нет ограничений на количество Listagg в одной групировке.
with test_data as
(
select ‘A’ as col1, ‘T_a1’ as col2, ‘123’ as col3 from dual
union select ‘A’, ‘T_a1’, ‘456’ from dual
union select ‘A’, ‘T_a1’, ‘789’ from dual
union select ‘A’, ‘T_a2’, ‘123’ from dual
union select ‘A’, ‘T_a2’, ‘456’ from dual
union select ‘A’, ‘T_a2’, ‘111’ from dual
union select ‘A’, ‘T_a3’, ‘999’ from dual
union select ‘B’, ‘T_a1’, ‘123’ from dual
union select ‘B’, ‘T_b1’, ‘740’ from dual
union select ‘B’, ‘T_b1’, ‘846’ from dual
)
select col1
, (select listagg(column_value, ‘,’) within group (order by column_value desc) from table(collect_col2)) as col2s
, (select listagg(column_value, ‘,’) within group (order by column_value desc) from table(collect_col3)) as col3s
from
(
select col1,
collect(distinct col2) as collect_col2,
collect(distinct col3) as collect_col3
from test_data
group by col1
);
Is it possible to search every field of every table for a particular value in Oracle?
There are hundreds of tables with thousands of rows in some tables so I know this could take a very long time to query. But the only thing I know is that a value for the field I would like to query against is 1/22/2008P09RR8 .
I've tried using this statement below to find an appropriate column based on what I think it should be named but it returned no results.
There is absolutely no documentation on this database and I have no idea where this field is being pulled from.
@LalitKumarB The page you listed is no longer accessible. Would it be possible to post some information as an answer?
Читайте также: