10 CLARK

10 KING,CLARK

10 MILLER,KING,CLARK

20 SMITH

20 JONES,SMITH

сив LIST должен быть возвращен первый служащий соответственно рангам по DEPTNO и ENAME. Если значение PRIORDEPTNO не NULL, присоединяем значение LIST предыдущего служащего к имени текущего служащего (массив ENAME) и затем возвращаем этот результат как LIST текущего служащего. Выполнение этой операции выражения CASE для каждой строки с заданным значением DEPTNO обеспечивает итеративное создание списка разделенных запятыми значений (comma-separated values, CSV). Промежуточные результаты можно увидеть в следующем примере:

select deptno, list

from ( select *

from (

select deptno,empno,ename,

lag(deptno)over(partition by deptno

order by empno) prior deptno

from emp )

model dimension by (

deptno,

row number()over(partition by deptno order by empno) rn

measures (

ename,

prior deptno,cast(null as varchar2(60)) list, count(*)over(partition by deptno) cnt, row number()over(partition by deptno order by empno) rnk

rules (

list[any,any]

order by deptno,rn = case when prior deptno[cv(),cv()] is null

then ename[cv(),cv()] else ename[cv(),cv()],

list[cv(),rnk[cv(),cv()]-1]

DEPTNO LIST

20 SCOTT,JONES,SMITH

20 ADAMS,SCOTT,JONES,SMITH

20 FORD,ADAMS,SCOTT,JONES,SMITH

30 ALLEN

30 WARD,ALLEN

30 MARTIN,WARD,ALLEN

30 BLAKE,MARTIN,WARD,ALLEN

30 TURNER,BLAKE,MARTIN,WARD,ALLEN

30 JAMES,TURNER,BLAKE,MARTIN,WARD,ALLEN

Последний шаг - выбрать в каждом отделе только последнего служащего, что гарантирует получение полного списка служащих отдела, в котором они перечислены через запятую. Чтобы возвратить только полные списки служащих каждого отдела, используем значения массивов CNT и RN. Поскольку RN представляет ранги служащих каждого отдела по EMPNO, последним служащим в отделе будет служащий, для которого CNT = RN, как показывает следующий пример:

select deptno,

list

from ( select * from (

select deptno,empno,ename,

lag(deptno)over(partition by deptno

order by empno) prior deptno

from emp )

model dimension by (

deptno,

row number()over(partition by deptno order by empno) rn

measures (

ename,

prior deptno,cast(null as varchar2(60)) list, count(*)over(partition by deptno) cnt, row number()over(partition by deptno order by empno) rnk

rules (

list[any,any]

order by deptno,rn = case when prior deptno[cv(),cv()] is null

then ename[cv(),cv()] else ename[cv(),cv()],

list[cv(),rnk[cv(),cv()]-1]

where cnt = rn

DEPTNO LIST

10 MILLER,KING,CLARK

20 FORD,ADAMS,SCOTT,JONES,SMITH

30 JAMES,TURNER,BLAKE,MARTIN,WARD,ALLEN

Выбор текста, не соответствующего шаблону (Oracle) Задача

Имеется текстовое поле, содержащее некоторый структурированный текст (например, телефонные номера), и требуется найти неправильно структурированные элементы. Например, есть следующие данные:

select emp id, text from employee comment

EMP ID TEXT

7369 126 Varnum, Edmore MI 48829, 989 313-5351

7499 1105 McConnell Court Cedar Lake MI 48812 Home: 989-387-4321

Cell: (237) 438-3333

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

7369 126 Varnum, Edmore MI 48829, 989 313-5351

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

Решение

Решение этой задачи состоит из нескольких частей:

1. Описываем множество истинных телефонных номеров.

2. Исключаем из рассмотрения все правильно форматированные телефонные номера.

3. Проверяем, остались ли еще какие-либо телефонные номера. Если остались, значит, именно они форматированы неправильно.

Следующее решение выгодно использует регулярные выражения, введенные в Oracle Database 10g:

select emp id, text from employee comment

where regexp like(text, [0-9]{3}[-. ][0-9]{3}[-. ][0-9]{4})