d20[any] = case when deptno[cv()]=20 then d20[cv()] else 0 end, d30[any] = case when deptno[cv()]=30 then d30[cv()] else 0 end

Теперь вы должны понять, что оператор MODEL возвращает такое же результирующее множество, что и вложенный запрос, только результатам операции COUNT присвоены псевдонимы D10, D20 и D30. Следующий запрос доказывает это:

select deptno, count(*) d10, count(*) d20, count(*) d30 from emp group by deptno

Итак, оператор MODEL взял значения для DEPTNO и CNT, поместил их в массивы и затем обеспечил представление каждого массива отдельным DEPTNO. На данный момент каждый массив, D10, D20 и D30, имеет одно отличное от нуля значение, представляющее CNT для данного DEPTNO. Результирующее множество уже транспонировано. Осталось только применить агрегатную функцию MAX (можно было бы использовать MIN или SUM; в данном случае нет никакий разницы), чтобы возвратить только одну строку:

select max(d10) d10, max(d20) d20,

max(d30) d30

from ( select d10,d20,d30

from ( select deptno, count(*) cnt from emp group by deptno ) model

dimension by(deptno d)

measures(deptno, cnt d10, cnt d20, cnt d30) rules(

d10[any] = case when deptno[cv()]=10 then d10[cv()] else 0 end,

d20[any] = case when deptno[cv()]=20 then d20[cv()] else 0 end,

d30[any] = case when deptno[cv()]=30 then d30[cv()] else 0 end

Извлечение элементов строки, положение которых в строке неизвестно

Задача

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

xxxxxabc[867]xxx[-]xxxx[5309]xxxxx xxxxxtime:[11271978]favnum:[4]id:[Joe]xxxxx call:[F GET ROWS()]b1:[ROSEWOOD...SIR]b2:[44400002]77.90xxxxx film:[non marked]qq:[unit]tailpipe:[withabanana?]80sxxxxx

Требуется извлечь значения, заключенные в квадратные скобки, и получить следующее результирующее множество:

FIRST VAL SECOND VAL LAST VAL

867 - 5309

11271978 4 Joe

F GET ROWS() ROSEWOOD...SIR 44400002 non marked unit withabanana?

Решение

Несмотря на отсутствие информации о точном местоположении интересующих нас значений, известно, что они заключены в квадратные скобки, [], и что их всего три. С помощью встроенной функции Oracle INSTR найдите местоположения скобок и, используя встроенную функцию SUBSTR, извлеките значения из строки. Представление V будет содержать строки, подлежащие синтаксическому разбору, и определяется следующим образом (оно используется исключительно для удобства чтения):

create view V as

select xxxxxabc[867]xxx[-]xxxx[5309]xxxxx msg from dual union all

select xxxxxtime:[11271978]favnum:[4]id:[Joe]xxxxx msg from dual union all

select call:[F GET ROWS()]b1:[ROSEWOOD...SIR]b2:[44400002]77.90xxxxx msg from dual union all

select film:[non marked]qq:[unit]tailpipe:[withabanana?]80sxxxxx msg

from dual

1 select substr(msg,

2 instr(msg,

3 instr(msg,],1,1)-instr(msg,[,1,1)-1) first val,

4 substr(msg,

5 instr(msg,[,1,2)+1,

6 instr(msg,],1,2)-instr(msg,[,1,2)-1) second val,

7 substr(msg,

8 instr(msg,[

9 instr(msg,],-1,1)-instr(msg,[,-1,1)-1) last val 10 from V

Обсуждение

Применение встроенной функции Oracle INSTR значительно упрощает решение этой задачи. Поскольку известно, что искомые значения заключены в [] и что в строке три набора [], первый шаг решения -с помощью INSTR найти числовые позиции [] в каждой строке. Следующий пример возвращает числовые позиции открывающих и закрывающих скобок в каждой строке:

select instr(msg,[,1,1) 1st [ , instr(msg,],1,1) ] 1st , instr(msg,[,1,2) 2nd [ , instr(msg,],1,2) ] 2nd , instr(msg,[,-1,1) 3rd [ , instr(msg,],-1,1) ] 3rd from V

1st [ ] 1st

2nd [ ] 2nd

3rd [ ] 3rd

Вся тяжелая работа выполнена. Осталось лишь вставить полученные числовые позиции в SUBSTR и разобрать MSG. В законченном примере можно заметить, что над значениями, возвращаемыми INSTR, выполняются простые арифметические действия, в частности, +1 и -1. Это гарантирует, что в окончательном результирующем множестве не будет открывающей квадратной скобки [. Ниже представлено решение без добавления и вычитания 1 из возвращаемых INSTR значений. Обратите внимание, что в этом случае каждое значение начинается с открывающей квадратной скобки:

select substr(msg,

instr(msg,[,1,1),

instr(msg,],1,1)-instr(msg,[,1,1)) first val, substr(msg, instr(msg,[,1,2),