вторник, 27 ноября 2012 г.

О соединении данных столбца в одну строку с помощью SQL-запроса

Постановка задачи: в некоей таблице имеется столбец с данными. Необходимо эти данные склеить в строку с помощью одного SQL-запроса.

Часть I. Аналитика и иерархия.

Простой способ решения задачи подсказал Т.Кайт в статье On Ignoring, Locking, and Parsing. Этот способ основывается на использовании аналитических функций и функции SYS_CONNECT_BY_PATH для иерархических запросов и применим начиная с Oracle9i Database Release 1. Идея заключается в следующем.

Выбираемые данные необходимо разделить на подгруппы, для которых будут склеиваться строки и в каждой подгруппе пронумеровать элементы в порядке их склеивания.
```
  select
    deptno,
    ename,
    row_number() over (partition by deptno order by ename) rn
  from emp
```
С помощью иерархического запроса все данные подгруппы соединяются в одну ветку дерева. Для каждого узла с помощью функции SYS_CONNECT_BY_PATH вычисляется строка со всеми предшествующими значениями поля, разделенными заданным разделителем.
```
select
  deptno,
  sys_connect_by_path(ename, ' ' ) scbp
from
  (select
     deptno,
     ename,
     row_number() over (partition by deptno order by ename) rn
   from emp
  )
start with rn = 1
connect by prior rn = rn-1
and prior deptno = deptno
```

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

select
  deptno,
  max(sys_connect_by_path(ename, ' ' )) scbp
from
  (select
     deptno,
     ename,
     row_number() over (partition by deptno order by ename) rn
   from emp
  )
start with rn = 1
connect by prior rn = rn-1
and prior deptno = deptno
group by deptno
order by deptno

В результате получаем список сотрудников по отделам.

   DEPTNO         SCBP
---------         ----------------------------------
       10         CLARK KING MILLER
       20         ADAMS FORD JONES SCOTT ...
       30         ALLEN BLAKE JAMES MARTIN ...

Этот способ прост и удобен, но имеет один недостаток. Функция SYS_CONNECT_BY_PATH работает с переменными VARCHAR2, которые, как известно, ограничиваются размером 4000 символов. При большом объеме склеиваемых данных, неизбежно возникает ошибка ORA-01489: result of string concatenation is too long. Эту проблему решает второй способ решения задачи.

Часть II. XML и CLOB.

Второй вариант предусматривает использование аггрегатной функции XMLAGG для получения склеенной строки в виде XML-объекта, который может быть преобразован в CLOB.

select
  deptno,
  XMLAGG(XMLELEMENT("node", ename)).getCLOBVal() scbp
from emp
group by deptno

Для упорядочивания элементов в склеиваемой строке функции XMLAGG можно добавить сортировку.

select
  deptno,
  XMLAGG(XMLELEMENT("node", ename) order by ename).getCLOBVal() scbp
from emp
group by deptno

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

select
  deptno,
  XMLAGG(XMLELEMENT("node", ename) order by ename).getCLOBVal() scbp
from emp
group by deptno
order by cast( substr(scbp, 1, 4000) as varchar2(4000))

В результате получится следующее:

   DEPTNO         SCBP
---------         ----------------------------------
       20         <node>ADAMS</node><node>FORD</node> ...
       30         <node>ALLEN</node><node>BLAKE</node> ...
       10         <node>CLARK</node><node>KING</node>

Недостаток данного способа – наличие тэгов, которые необходимо обрабатывать в зависимости от целей поставленной задачи. Хотя для случая, ставшего причиной поиска описанного решения, тэги оказались скорее плюсом.
Этот же метод можно применить и для более коротких строк, где не требуется использование CLOB-данных. XML-объект можно преобразовать в varchar2 с помощью функции getStringVal().

select
  deptno,
  XMLAGG(XMLELEMENT("node", ename) order by ename).getStringVal() scbp
from emp
group by deptno
order by scbp

среда, 14 ноября 2012 г.

Окно - Исходный формат отчета отличается от конечного

Итак, если создан отчет с шаблоном в формате RTF, а вывод в виде Excel, и выпадает окошко с сообщением, что исходный формат отчета отличается от конечного, а нам это окошко надо убрать с глаз долой, тогда правим:

В регистре:
HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security
новый параметр (если нету) с типом DWORD , называем ExtensionHardening - значение 0

вторник, 6 ноября 2012 г.