ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Не работает подзапрос multiset и отбратно
28 сообщений из 28, показаны все 2 страниц
  все  
Не работает подзапрос multiset и отбратно
    #39558792
Фотография barrabas
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
barrabas
Участник
Oracle 11.2.04

вот тестовый запрос, суть в том что есть много разных строк-кодограмм (данные через разделитель) и в зависимости от типа, парсить нужно по разному.
система сторонняя, т.к. запрос должен использовать только стандартные типы (пакеты/типы создавать нельзя).

идея общего решения такая.
PARSING - определяет на каком месте по порядку находится то или иное значение для определенного типа кодограммы
далее идет парсинг строки на строки-значения и упаковка во вложенную таблицу
потом подзапросом берется значение с нужным номером строки в зависимости какой нужен тэг и какой тип кодограммы.

но работает только для первого элемента, хотя если делать count/max/min в подзапросах то видно что значения там есть и нужное кол-во строк во вложенной таблице.
не понимаю что за глюк

PS было решение через xml парсинг (заменя разделитея на тэги и получение значения через '/c/a[@num='|| OPER_TYPE ||']/text()'), но скорость не устроила, хотел так попробовать.


Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
with PARSING as 
(select  1 ID_RECORD_TYPE,  1 OPER_DATE,       5 GOODS_ID,      6 PAY_GOODS_ID, 15 OPER_PRICE,
         2 OPER_TYPE,      12 PAY_OPER_VALUE, 11 OPER_VALUE,   -1 SERIES_NUM,   17 OBJECT_NUM,
        18 ISSUER_NUM,     19 FILIAL_NUM,     20 TERM_ISS_NUM, 21 TERM_FIL_NUM, 22 TERM_NUM    
   from dual 
  union all
 select 22 ID_RECORD_TYPE,  2 OPER_DATE,       4 GOODS_ID,     -1 PAY_GOODS_ID,  5 OPER_PRICE,
         3 OPER_TYPE,      -1 PAY_OPER_VALUE,  6 OPER_VALUE,    7 SERIES_NUM,    8 OBJECT_NUM,
         9 ISSUER_NUM,     10 FILIAL_NUM,     11 TERM_ISS_NUM, 12 TERM_FIL_NUM, 13 TERM_NUM    
   from dual 
  union all
 select 11 ID_RECORD_TYPE,  3 OPER_DATE,      17 GOODS_ID,     18 PAY_GOODS_ID, -1 OPER_PRICE,
        21 OPER_TYPE,      -1 PAY_OPER_VALUE, -1 OPER_VALUE,   -1 SERIES_NUM,    4 OBJECT_NUM,
        11 ISSUER_NUM,     12 FILIAL_NUM,     13 TERM_ISS_NUM, 14 TERM_FIL_NUM,  5 TERM_NUM    
   from dual 
  union all
 select 13 ID_RECORD_TYPE,  1 OPER_DATE,      12 GOODS_ID,     13 PAY_GOODS_ID, -1 OPER_PRICE,
        -1 OPER_TYPE,      16 PAY_OPER_VALUE, -1 OPER_VALUE,   -1 SERIES_NUM,    3 OBJECT_NUM,
         4 ISSUER_NUM,      6 FILIAL_NUM,      5 TERM_ISS_NUM,  7 TERM_FIL_NUM,  8 TERM_NUM    
   from dual
), BASE_DATA as
(select k.ID_RECORD_TYPE,
        k.CODOGRAMM, 
        cast(multiset(select regexp_substr(k.CODOGRAMM,
                            '[^~]+', 1, rownum) 
                      from dual           
                  connect by rownum <= 30) as SYS.Odcivarchar2list) CODES_TBL
   from (select 1 ID_RECORD_TYPE, '20170723125638~32~4~1~15~1~4~1~3870~3870~3800~147060~1~0~3870~147060~426339080~426~1~512~1~404~~404~~0~~0~A5CFE3AFD5F4418693FDFFFD69AE5C90~~~~~~~~~~~' CODOGRAMM
           from DUAL
          union all 
         select 1 ID_RECORD_TYPE, '20170619204900~32~19~1~60~1~19~1~3900~3900~2564~99996~1~0~3900~99996~426339080~426~1~512~1~404~~404~~0~~0~0FBA9FCCA1BB4F82BAD119652E360AEA~~~~~~~~~~~' CODOGRAMM
           from DUAL           
        ) k
  where 1=1
    and k.ID_RECORD_TYPE in (select ID_RECORD_TYPE from PARSING)
)
--select * from BASE_DATA
select k.ID_RECORD_TYPE,
       k.CODOGRAMM,
       k.CODES_TBL, 
       (select x.COLUMN_VALUE from table(cast(CODES_TBL as SYS.Odcivarchar2list)) x where rownum = p.OPER_DATE)       OPER_DATE, 
       (select x.COLUMN_VALUE from table(cast(CODES_TBL as SYS.Odcivarchar2list)) x where rownum = p.OPER_TYPE)       OPER_TYPE,
       (select x.COLUMN_VALUE from table(cast(CODES_TBL as SYS.Odcivarchar2list)) x where rownum = p.GOODS_ID)        GOODS_ID,
       (select x.COLUMN_VALUE from table(cast(CODES_TBL as SYS.Odcivarchar2list)) x where rownum = p.PAY_GOODS_ID)    PAY_GOODS_ID,
       (select x.COLUMN_VALUE from table(cast(CODES_TBL as SYS.Odcivarchar2list)) x where rownum = p.OPER_PRICE)      OPER_PRICE,
       (select x.COLUMN_VALUE from table(cast(CODES_TBL as SYS.Odcivarchar2list)) x where rownum = p.PAY_OPER_VALUE)  PAY_OPER_VALUE,
       (select x.COLUMN_VALUE from table(cast(CODES_TBL as SYS.Odcivarchar2list)) x where rownum = p.OPER_VALUE)      OPER_VALUE,
       (select x.COLUMN_VALUE from table(cast(CODES_TBL as SYS.Odcivarchar2list)) x where rownum = p.SERIES_NUM)      SERIES_NUM,
       (select x.COLUMN_VALUE from table(cast(CODES_TBL as SYS.Odcivarchar2list)) x where rownum = p.OBJECT_NUM)      OBJECT_NUM,
       (select x.COLUMN_VALUE from table(cast(CODES_TBL as SYS.Odcivarchar2list)) x where rownum = p.ISSUER_NUM)      ISSUER_NUM,
       (select x.COLUMN_VALUE from table(cast(CODES_TBL as SYS.Odcivarchar2list)) x where rownum = p.FILIAL_NUM)      FILIAL_NUM,
       (select x.COLUMN_VALUE from table(cast(CODES_TBL as SYS.Odcivarchar2list)) x where rownum = p.TERM_ISS_NUM)    TERM_ISS_NUM,
       (select x.COLUMN_VALUE from table(cast(CODES_TBL as SYS.Odcivarchar2list)) x where rownum = p.TERM_FIL_NUM)    TERM_FIL_NUM,
       (select x.COLUMN_VALUE from table(cast(CODES_TBL as SYS.Odcivarchar2list)) x where rownum = p.TERM_NUM)        TERM_NUM
  from BASE_DATA k, PARSING p  
 where k.ID_RECORD_TYPE = p.ID_RECORD_TYPE
...
Рейтинг: 0 / 0
24.11.2017, 10:31
| Ответить | Цитировать | Написать  
Не работает подзапрос multiset и отбратно
    #39558799
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
barrabasно работает только для первого элемента,
Код: plsql
1.
where rownum =

RTFM Conditions testing for ROWNUM values greater than a positive integer are always … (FAQ)
...
Рейтинг: 0 / 0
24.11.2017, 10:36
| Ответить | Цитировать | Написать  
Не работает подзапрос multiset и отбратно
    #39558800
Фотография barrabas
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
barrabas
Участник
Берется только первое значение из первой строки
...
Рейтинг: 0 / 0
24.11.2017, 10:39
| Ответить | Цитировать | Написать  
Не работает подзапрос multiset и отбратно
    #39558801
Фотография barrabas
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
barrabas
Участник
Elicbarrabasно работает только для первого элемента,
Код: plsql
1.
where rownum =

RTFM Conditions testing for ROWNUM values greater than a positive integer are always … (FAQ)
да с rownum тупанул, согласен
...
Рейтинг: 0 / 0
24.11.2017, 10:43
| Ответить | Цитировать | Написать  
Не работает подзапрос multiset и отбратно
    #39558806
Фотография barrabas
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
barrabas
Участник
почему для каждой строки одна и таже вложенная таблица, это видно по значению первого поля
...
Рейтинг: 0 / 0
24.11.2017, 10:46
| Ответить | Цитировать | Написать  
Не работает подзапрос multiset и отбратно
    #39558808
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
barrabasно скорость не устроилаСопровождаемость vs говнокодистось
...
Рейтинг: 0 / 0
24.11.2017, 10:50
| Ответить | Цитировать | Написать  
Не работает подзапрос multiset и отбратно
    #39558815
Фотография barrabas
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
barrabas
Участник
Elicbarrabasно скорость не устроилаСопровождаемость vs говнокодистось

Elicbarrabasно скорость не устроилаСопровождаемость vs говнокодистось
да, я с ровнумом лоханулся, что-то меня в массивы переклинило, начал рассматривать выборку не как таблицу а как коллкецию, а ровнум индексом :)

именно и хочется сделать и быстро и сопровождаемость чтобы осталась .
подумаю над substr/instr с регулярками еще

но по поводу что всегда ссылка на одну коллекцию же - это баг или я опять пропустил RTFM какой?
...
Рейтинг: 0 / 0
24.11.2017, 10:57
| Ответить | Цитировать | Написать  
Не работает подзапрос multiset и отбратно
    #39558823
Фотография barrabas
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
barrabas
Участник
Код: plsql
1.
2.
3.
4.
5.
6.
7.
....
select k.ID_RECORD_TYPE,
       k.CODOGRAMM,
       k.CODES_TBL, 
       (select max(x.COLUMN_VALUE) from table(cast(CODES_TBL as SYS.Odcivarchar2list)) x )       MV       
  from BASE_DATA k, PARSING p  
 where k.ID_RECORD_TYPE = p.ID_RECORD_TYPE



разве не хрень?
...
Рейтинг: 0 / 0
24.11.2017, 11:02
| Ответить | Цитировать | Написать  
Не работает подзапрос multiset и отбратно
    #39558827
Фотография barrabas
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
barrabas
Участник
во второй строке же A5CFE3AFD5F4418693FDFFFD69AE5C90 должно быть
...
Рейтинг: 0 / 0
24.11.2017, 11:05
| Ответить | Цитировать | Написать  
Не работает подзапрос multiset и отбратно
    #39558850
Фотография barrabas
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
barrabas
Участник
сама задача просто на regexp_substr(k.CODOGRAMM, '[^~]+', 1, ПОЗИЦИЯ) решается :), с небольшим анализом на несуществующие теги :).
просто переписывание XML парсинга не туда направило полет мысли.
...
Рейтинг: 0 / 0
24.11.2017, 11:29
| Ответить | Цитировать | Написать  
Не работает подзапрос multiset и отбратно
    #39558945
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
barrabas,

Код: plsql
1.
       (select x.COLUMN_VALUE from table(cast(CODES_TBL as SYS.Odcivarchar2list)) x where rownum = p.OPER_DATE)       OPER_DATE,



если я понял о чем речь, будет намного проще и быстрее
subsr + instr ~

ps
просто интересно хмл как использавали что долго?


.....
stax
...
Рейтинг: 0 / 0
24.11.2017, 12:53
| Ответить | Цитировать | Написать  
Не работает подзапрос multiset и отбратно
    #39558992
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Начните с простого:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
with codogramm_meta as 
(select  1 ID_RECORD_TYPE,  1 OPER_DATE,       5 GOODS_ID,      6 PAY_GOODS_ID, 15 OPER_PRICE,
         2 OPER_TYPE,      12 PAY_OPER_VALUE, 11 OPER_VALUE,   -1 SERIES_NUM,   17 OBJECT_NUM,
        18 ISSUER_NUM,     19 FILIAL_NUM,     20 TERM_ISS_NUM, 21 TERM_FIL_NUM, 22 TERM_NUM    
   from dual 
  union all
 select 22 ID_RECORD_TYPE,  2 OPER_DATE,       4 GOODS_ID,     -1 PAY_GOODS_ID,  5 OPER_PRICE,
         3 OPER_TYPE,      -1 PAY_OPER_VALUE,  6 OPER_VALUE,    7 SERIES_NUM,    8 OBJECT_NUM,
         9 ISSUER_NUM,     10 FILIAL_NUM,     11 TERM_ISS_NUM, 12 TERM_FIL_NUM, 13 TERM_NUM    
   from dual 
  union all
 select 11 ID_RECORD_TYPE,  3 OPER_DATE,      17 GOODS_ID,     18 PAY_GOODS_ID, -1 OPER_PRICE,
        21 OPER_TYPE,      -1 PAY_OPER_VALUE, -1 OPER_VALUE,   -1 SERIES_NUM,    4 OBJECT_NUM,
        11 ISSUER_NUM,     12 FILIAL_NUM,     13 TERM_ISS_NUM, 14 TERM_FIL_NUM,  5 TERM_NUM    
   from dual 
  union all
 select 13 ID_RECORD_TYPE,  1 OPER_DATE,      12 GOODS_ID,     13 PAY_GOODS_ID, -1 OPER_PRICE,
        -1 OPER_TYPE,      16 PAY_OPER_VALUE, -1 OPER_VALUE,   -1 SERIES_NUM,    3 OBJECT_NUM,
         4 ISSUER_NUM,      6 FILIAL_NUM,      5 TERM_ISS_NUM,  7 TERM_FIL_NUM,  8 TERM_NUM    
   from dual
), codogramm as
(select 1 ID_RECORD_TYPE,     '20170723125638~32~4~1~15~1~4~1~3870~3870~3800~147060~1~0~3870~147060~426339080~426~1~512~1~404~~404~~0~~0~A5CFE3AFD5F4418693FDFFFD69AE5C90~~~~~~~~~~~' CODOGRAMM
from DUAL union all select 1, '20170619204900~32~19~1~60~1~19~1~3900~3900~2564~99996~1~0~3900~99996~426339080~426~1~512~1~404~~404~~0~~0~0FBA9FCCA1BB4F82BAD119652E360AEA~~~~~~~~~~~' CODOGRAMM
           from DUAL           
)
,codogramm_x as (select ID_RECORD_TYPE, '~'||CODOGRAMM||'~' CODOGRAMM from CODOGRAMM) 
select c.id_record_type
, substr(c.codogramm, instr(c.codogramm,'~',1,m.OPER_DATE)+1, instr(c.codogramm,'~',1,m.OPER_DATE+1)-instr(c.codogramm,'~',1,m.OPER_DATE)-1) OPER_DATE
, substr(c.codogramm, instr(c.codogramm,'~',1,m.OPER_TYPE)+1, instr(c.codogramm,'~',1,m.OPER_TYPE+1)-instr(c.codogramm,'~',1,m.OPER_TYPE)-1) OPER_TYPE
, substr(c.codogramm, instr(c.codogramm,'~',1,m.GOODS_ID)+1, instr(c.codogramm,'~',1,m.GOODS_ID+1)-instr(c.codogramm,'~',1,m.GOODS_ID)-1) GOODS_ID
, substr(c.codogramm, instr(c.codogramm,'~',1,m.PAY_OPER_VALUE)+1, instr(c.codogramm,'~',1,m.PAY_OPER_VALUE+1)-instr(c.codogramm,'~',1,m.PAY_OPER_VALUE)-1) PAY_OPER_VALUE
from codogramm_meta m, codogramm_x c
where c.id_record_type=m.id_record_type
;
...
Рейтинг: 0 / 0
24.11.2017, 13:36
| Ответить | Цитировать | Написать  
Не работает подзапрос multiset и отбратно
    #39559002
Фотография barrabas
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
barrabas
Участник
Staxbarrabas,

Код: plsql
1.
       (select x.COLUMN_VALUE from table(cast(CODES_TBL as SYS.Odcivarchar2list)) x where rownum = p.OPER_DATE)       OPER_DATE,



если я понял о чем речь, будет намного проще и быстрее
subsr + instr ~

ps
просто интересно хмл как использавали что долго?


.....
stax

дали на переработку старую прогу, нужно было много чего добавить/исправить. начал проверять запрос, как-то долго показалось именно парсинг.
вид следующий (это не реальный запрос, а действиующий пример, как парсили)

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
with PARSING as 
(select  1 ID_RECORD_TYPE,  1 OPER_DATE,       5 GOODS_ID,      6 PAY_GOODS_ID, 15 OPER_PRICE,
         2 OPER_TYPE,      12 PAY_OPER_VALUE, 11 OPER_VALUE,   -1 SERIES_NUM,   17 OBJECT_NUM,
        18 ISSUER_NUM,     19 FILIAL_NUM,     20 TERM_ISS_NUM, 21 TERM_FIL_NUM, 22 TERM_NUM    
   from dual 
  union all
 select 22 ID_RECORD_TYPE,  2 OPER_DATE,       4 GOODS_ID,     -1 PAY_GOODS_ID,  5 OPER_PRICE,
         3 OPER_TYPE,      -1 PAY_OPER_VALUE,  6 OPER_VALUE,    7 SERIES_NUM,    8 OBJECT_NUM,
         9 ISSUER_NUM,     10 FILIAL_NUM,     11 TERM_ISS_NUM, 12 TERM_FIL_NUM, 13 TERM_NUM    
   from dual 
  union all
 select 11 ID_RECORD_TYPE,  3 OPER_DATE,      17 GOODS_ID,     18 PAY_GOODS_ID, -1 OPER_PRICE,
        21 OPER_TYPE,      -1 PAY_OPER_VALUE, -1 OPER_VALUE,   -1 SERIES_NUM,    4 OBJECT_NUM,
        11 ISSUER_NUM,     12 FILIAL_NUM,     13 TERM_ISS_NUM, 14 TERM_FIL_NUM,  5 TERM_NUM    
   from dual 
  union all
 select 13 ID_RECORD_TYPE,  1 OPER_DATE,      12 GOODS_ID,     13 PAY_GOODS_ID, -1 OPER_PRICE,
        -1 OPER_TYPE,      16 PAY_OPER_VALUE, -1 OPER_VALUE,   -1 SERIES_NUM,    3 OBJECT_NUM,
         4 ISSUER_NUM,      6 FILIAL_NUM,      5 TERM_ISS_NUM,  7 TERM_FIL_NUM,  8 TERM_NUM    
   from dual
), BASE_DATA as
(select k.ID_RECORD_TYPE,
        k.CODOGRAMM, 
        (select xmltype('<c>' || wm_concat('<a num="' || rownum || '">'|| extractvalue(xt.column_value,'e') || '</a>') || '</c>') A
                          from table(xmlsequence(extract( xmltype('<c><e>' || replace(CODOGRAMM,'~','</e><e>') || '</e></c>'), '/c/e') )) xt) EXCHANGE_STRING1
        
   from (select 1 ID_RECORD_TYPE, '20170723125638~32~4~1~15~1~4~1~3870~3870~3800~147060~1~0~3870~147060~426339080~426~1~512~1~404~~404~~0~~0~A5CFE3AFD5F4418693FDFFFD69AE5C90~~~~~~~~~~~' CODOGRAMM
           from DUAL
          union all 
         select 1 ID_RECORD_TYPE, '20170619204900~32~19~1~60~1~19~1~3900~3900~2564~99996~1~0~3900~99996~426339080~426~1~512~1~404~~404~~0~~0~0FBA9FCCA1BB4F82BAD119652E360AEA~~~~~~~~~~~' CODOGRAMM
           from DUAL           
        ) k
  where 1=1
    and k.ID_RECORD_TYPE in (select ID_RECORD_TYPE from PARSING)
)
--select * from BASE_DATA
select k.ID_RECORD_TYPE,
       k.CODOGRAMM,
               to_date(EXTRACT(EXCHANGE_STRING1, '/c/a[@num='|| OPER_DATE ||']/text()'), 'yyyymmddhh24miss')  OPER_DATE,
               to_number(EXTRACT(EXCHANGE_STRING1, '/c/a[@num='|| OPER_TYPE ||']/text()'))                    OPER_TYPE,
               to_number(EXTRACT(EXCHANGE_STRING1, '/c/a[@num='|| GOODS_ID ||']/text()'))                     GOODS_ID,
               to_number(EXTRACT(EXCHANGE_STRING1, '/c/a[@num='|| PAY_GOODS_ID ||']/text()'))                 PAY_GOODS_ID,
               to_number(EXTRACT(EXCHANGE_STRING1, '/c/a[@num='|| OPER_PRICE ||']/text()')) / 100             OPER_PRICE,
               to_number(EXTRACT(EXCHANGE_STRING1, '/c/a[@num='|| PAY_OPER_VALUE ||']/text()')) / 100         PAY_OPER_VALUE,
               to_number(EXTRACT(EXCHANGE_STRING1, '/c/a[@num='|| OPER_VALUE ||']/text()')) / 100             OPER_VALUE,
               to_number(EXTRACT(EXCHANGE_STRING1, '/c/a[@num='|| SERIES_NUM ||']/text()'))                   SERIES_NUM,
               to_number(EXTRACT(EXCHANGE_STRING1, '/c/a[@num='|| OBJECT_NUM ||']/text()'))                   OBJECT_NUM,
               to_number(EXTRACT(EXCHANGE_STRING1, '/c/a[@num='|| ISSUER_NUM ||']/text()'))                   ISSUER_NUM,
               to_number(EXTRACT(EXCHANGE_STRING1, '/c/a[@num='|| FILIAL_NUM ||']/text()'))                   FILIAL_NUM,
               to_number(EXTRACT(EXCHANGE_STRING1, '/c/a[@num='|| TERM_ISS_NUM ||']/text()'))                 TERM_ISS_NUM,
               to_number(EXTRACT(EXCHANGE_STRING1, '/c/a[@num='|| TERM_FIL_NUM ||']/text()'))                 TERM_FIL_NUM,
               to_number(EXTRACT(EXCHANGE_STRING1, '/c/a[@num='|| TERM_NUM ||']/text()'))                     TERM_NUM
          
  from BASE_DATA k, PARSING p  
 where k.ID_RECORD_TYPE = p.ID_RECORD_TYPE




поправил на regexp_substr
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
        with PARSING as 
        (select  1 ID_RECORD_TYPE,  1 OPER_DATE,       5 GOODS_ID,      6 PAY_GOODS_ID, 15 OPER_PRICE,
                 2 OPER_TYPE,      12 PAY_OPER_VALUE, 11 OPER_VALUE,   -1 SERIES_NUM,   17 OBJECT_NUM,
                18 ISSUER_NUM,     19 FILIAL_NUM,     20 TERM_ISS_NUM, 21 TERM_FIL_NUM, 22 TERM_NUM    
           from dual 
          union all
         select 22 ID_RECORD_TYPE,  2 OPER_DATE,       4 GOODS_ID,     -1 PAY_GOODS_ID,  5 OPER_PRICE,
                 3 OPER_TYPE,      -1 PAY_OPER_VALUE,  6 OPER_VALUE,    7 SERIES_NUM,    8 OBJECT_NUM,
                 9 ISSUER_NUM,     10 FILIAL_NUM,     11 TERM_ISS_NUM, 12 TERM_FIL_NUM, 13 TERM_NUM    
           from dual 
          union all
         select 11 ID_RECORD_TYPE,  3 OPER_DATE,      17 GOODS_ID,     18 PAY_GOODS_ID, -1 OPER_PRICE,
                21 OPER_TYPE,      -1 PAY_OPER_VALUE, -1 OPER_VALUE,   -1 SERIES_NUM,    4 OBJECT_NUM,
                11 ISSUER_NUM,     12 FILIAL_NUM,     13 TERM_ISS_NUM, 14 TERM_FIL_NUM,  5 TERM_NUM    
           from dual 
          union all
         select 13 ID_RECORD_TYPE,  1 OPER_DATE,      12 GOODS_ID,     13 PAY_GOODS_ID, -1 OPER_PRICE,
                -1 OPER_TYPE,      16 PAY_OPER_VALUE, -1 OPER_VALUE,   -1 SERIES_NUM,    3 OBJECT_NUM,
                 4 ISSUER_NUM,      6 FILIAL_NUM,      5 TERM_ISS_NUM,  7 TERM_FIL_NUM,  8 TERM_NUM    
           from dual
        ), BASE_DATA as
        (select k.TIP_KODOGRAMM ID_RECORD_TYPE,
                translate(k.kodogramma,chr(27),'~')  CODOGRAMM     
           from MG042601.ECFIL159 k
          where k.DATA_FORMIROVANIYA >= sysdate - 180
            and k.tip_kodogramm in (select ID_RECORD_TYPE from PARSING)
        )
        --select * from BASE_DATA
        select k.ID_RECORD_TYPE,
               k.CODOGRAMM,       
               decode(p.OPER_DATE,      -1, null, regexp_substr(k.CODOGRAMM, '[^~]+', 1, p.OPER_DATE))       OPER_DATE,
               decode(p.PAY_GOODS_ID,   -1, null, regexp_substr(k.CODOGRAMM, '[^~]+', 1, p.PAY_GOODS_ID))    PAY_GOODS_ID,
               decode(p.OPER_PRICE,     -1, null, regexp_substr(k.CODOGRAMM, '[^~]+', 1, p.OPER_PRICE))      OPER_PRICE,
               decode(p.PAY_OPER_VALUE, -1, null, regexp_substr(k.CODOGRAMM, '[^~]+', 1, p.PAY_OPER_VALUE))  PAY_OPER_VALUE,
               decode(p.OPER_VALUE,     -1, null, regexp_substr(k.CODOGRAMM, '[^~]+', 1, p.OPER_VALUE))      OPER_VALUE,
               decode(p.SERIES_NUM,     -1, null, regexp_substr(k.CODOGRAMM, '[^~]+', 1, p.SERIES_NUM))      SERIES_NUM,
               decode(p.OBJECT_NUM,     -1, null, regexp_substr(k.CODOGRAMM, '[^~]+', 1, p.OBJECT_NUM))      OBJECT_NUM,
               decode(p.ISSUER_NUM,     -1, null, regexp_substr(k.CODOGRAMM, '[^~]+', 1, p.ISSUER_NUM))      ISSUER_NUM,
               decode(p.FILIAL_NUM,     -1, null, regexp_substr(k.CODOGRAMM, '[^~]+', 1, p.FILIAL_NUM))      FILIAL_NUM,
               decode(p.TERM_ISS_NUM,   -1, null, regexp_substr(k.CODOGRAMM, '[^~]+', 1, p.TERM_ISS_NUM))    TERM_ISS_NUM,
               decode(p.TERM_FIL_NUM,   -1, null, regexp_substr(k.CODOGRAMM, '[^~]+', 1, p.TERM_FIL_NUM))    TERM_FIL_NUM,
               decode(p.TERM_NUM,       -1, null, regexp_substr(k.CODOGRAMM, '[^~]+', 1, p.TERM_NUM))        TERM_NUM
          from BASE_DATA k, PARSING p  
         where k.ID_RECORD_TYPE = p.ID_RECORD_TYPE
...
Рейтинг: 0 / 0
24.11.2017, 13:44
| Ответить | Цитировать | Написать  
Не работает подзапрос multiset и отбратно
    #39559016
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
barrabas
дали на переработку старую прогу, нужно было много чего добавить/исправить. начал проверять запрос, как-то долго показалось именно парсинг.
вид следующий (это не реальный запрос, а действиующий пример, как парсили)


спасибо
практики с хмл мало, собираю чужой опыт
парсится для каждой строки, мож потому долго

зы
я так понимаю счас EXTRACT депресед

зыы
ели есть время я б попробовал ora:tokenize для статистики ,
а так 20980873

....
stax
...
Рейтинг: 0 / 0
24.11.2017, 13:55
| Ответить | Цитировать | Написать  
Не работает подзапрос multiset и отбратно
    #39559021
Фотография barrabas
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
barrabas
Участник
Staxbarrabasдали на переработку старую прогу, нужно было много чего добавить/исправить. начал проверять запрос, как-то долго показалось именно парсинг.
вид следующий (это не реальный запрос, а действиующий пример, как парсили)


спасибо
практики с хмл мало, собираю чужой опыт
парсится для каждой строки, мож потому долго

зы
я так понимаю счас EXTRACT депресед

зыы
ели есть время я б попробовал ora:tokenize для статистики ,
а так 20980873

....
stax
в данном случае думаю уже некуда искорять.
если раньше 1000 записей выбиралась за 15-17 сек (а записей на порядки больше), реально ждали люди пару часов
с regexp_substr эти 1000 записей 1-1,5 сек
...
Рейтинг: 0 / 0
24.11.2017, 14:00
| Ответить | Цитировать | Написать  
Не работает подзапрос multiset и отбратно
    #39559023
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
barrabasв данном случае думаю уже некуда искорять.
если раньше 1000 записей выбиралась за 15-17 сек (а записей на порядки больше), реально ждали люди пару часов
с regexp_substr эти 1000 записей 1-1,5 сек

имхо 20980873 побыстрее будет

....
stax
...
Рейтинг: 0 / 0
24.11.2017, 14:04
| Ответить | Цитировать | Написать  
Не работает подзапрос multiset и отбратно
    #39559034
Фотография barrabas
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
barrabas
Участник
andrey_anonymousНачните с простого:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
with codogramm_meta as 
(select  1 ID_RECORD_TYPE,  1 OPER_DATE,       5 GOODS_ID,      6 PAY_GOODS_ID, 15 OPER_PRICE,
         2 OPER_TYPE,      12 PAY_OPER_VALUE, 11 OPER_VALUE,   -1 SERIES_NUM,   17 OBJECT_NUM,
        18 ISSUER_NUM,     19 FILIAL_NUM,     20 TERM_ISS_NUM, 21 TERM_FIL_NUM, 22 TERM_NUM    
   from dual 
  union all
 select 22 ID_RECORD_TYPE,  2 OPER_DATE,       4 GOODS_ID,     -1 PAY_GOODS_ID,  5 OPER_PRICE,
         3 OPER_TYPE,      -1 PAY_OPER_VALUE,  6 OPER_VALUE,    7 SERIES_NUM,    8 OBJECT_NUM,
         9 ISSUER_NUM,     10 FILIAL_NUM,     11 TERM_ISS_NUM, 12 TERM_FIL_NUM, 13 TERM_NUM    
   from dual 
  union all
 select 11 ID_RECORD_TYPE,  3 OPER_DATE,      17 GOODS_ID,     18 PAY_GOODS_ID, -1 OPER_PRICE,
        21 OPER_TYPE,      -1 PAY_OPER_VALUE, -1 OPER_VALUE,   -1 SERIES_NUM,    4 OBJECT_NUM,
        11 ISSUER_NUM,     12 FILIAL_NUM,     13 TERM_ISS_NUM, 14 TERM_FIL_NUM,  5 TERM_NUM    
   from dual 
  union all
 select 13 ID_RECORD_TYPE,  1 OPER_DATE,      12 GOODS_ID,     13 PAY_GOODS_ID, -1 OPER_PRICE,
        -1 OPER_TYPE,      16 PAY_OPER_VALUE, -1 OPER_VALUE,   -1 SERIES_NUM,    3 OBJECT_NUM,
         4 ISSUER_NUM,      6 FILIAL_NUM,      5 TERM_ISS_NUM,  7 TERM_FIL_NUM,  8 TERM_NUM    
   from dual
), codogramm as
(select 1 ID_RECORD_TYPE,     '20170723125638~32~4~1~15~1~4~1~3870~3870~3800~147060~1~0~3870~147060~426339080~426~1~512~1~404~~404~~0~~0~A5CFE3AFD5F4418693FDFFFD69AE5C90~~~~~~~~~~~' CODOGRAMM
from DUAL union all select 1, '20170619204900~32~19~1~60~1~19~1~3900~3900~2564~99996~1~0~3900~99996~426339080~426~1~512~1~404~~404~~0~~0~0FBA9FCCA1BB4F82BAD119652E360AEA~~~~~~~~~~~' CODOGRAMM
           from DUAL           
)
,codogramm_x as (select ID_RECORD_TYPE, '~'||CODOGRAMM||'~' CODOGRAMM from CODOGRAMM) 
select c.id_record_type
, substr(c.codogramm, instr(c.codogramm,'~',1,m.OPER_DATE)+1, instr(c.codogramm,'~',1,m.OPER_DATE+1)-instr(c.codogramm,'~',1,m.OPER_DATE)-1) OPER_DATE
, substr(c.codogramm, instr(c.codogramm,'~',1,m.OPER_TYPE)+1, instr(c.codogramm,'~',1,m.OPER_TYPE+1)-instr(c.codogramm,'~',1,m.OPER_TYPE)-1) OPER_TYPE
, substr(c.codogramm, instr(c.codogramm,'~',1,m.GOODS_ID)+1, instr(c.codogramm,'~',1,m.GOODS_ID+1)-instr(c.codogramm,'~',1,m.GOODS_ID)-1) GOODS_ID
, substr(c.codogramm, instr(c.codogramm,'~',1,m.PAY_OPER_VALUE)+1, instr(c.codogramm,'~',1,m.PAY_OPER_VALUE+1)-instr(c.codogramm,'~',1,m.PAY_OPER_VALUE)-1) PAY_OPER_VALUE
from codogramm_meta m, codogramm_x c
where c.id_record_type=m.id_record_type
;



так быстрее всего. быстрее регулярки раза в 1,5.
код конечно не очень читаемый, много мест где забыть скописастить нужное поле можно, но думаю так сделаю
я сразу думал об этом варианте, но как-то красоты хотелось, но 1,5-2 раза выше скорость победила
...
Рейтинг: 0 / 0
24.11.2017, 14:16
| Ответить | Цитировать | Написать  
Не работает подзапрос multiset и отбратно
    #39559038
Фотография barrabas
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
barrabas
Участник
Staxbarrabasв данном случае думаю уже некуда искорять.
если раньше 1000 записей выбиралась за 15-17 сек (а записей на порядки больше), реально ждали люди пару часов
с regexp_substr эти 1000 записей 1-1,5 сек

имхо 20980873 побыстрее будет

....
stax
да, протестил
мне показалось что много instr тоже не очень, но регулярка хуже.
...
Рейтинг: 0 / 0
24.11.2017, 14:18
| Ответить | Цитировать | Написать  
Не работает подзапрос multiset и отбратно
    #39559040
Фотография barrabas
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
barrabas
Участник
вот ситуация с multiset как-то напрягла, я помню использовал его давно в некоторых проектах, вот и думаю, правильно ли там работает
...
Рейтинг: 0 / 0
24.11.2017, 14:19
| Ответить | Цитировать | Написать  
Не работает подзапрос multiset и отбратно
    #39559048
Фотография barrabas
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
barrabas
Участник
andrey_anonymous,

спасибо, я бы заленился писать инстры для проверки скорости, остановился бы на регулярке.
...
Рейтинг: 0 / 0
24.11.2017, 14:28
| Ответить | Цитировать | Написать  
Не работает подзапрос multiset и отбратно
    #39559069
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
barrabasвот ситуация с multiset как-то напрягла, я помню использовал его давно в некоторых проектах, вот и думаю, правильно ли там работает
в оракаля where rownum = 3 не прокатит

получается надо обратно во вьюшку

имхо
можно строку распарсить (regular, substr, хмл ...) в таблицу и полученное соеденить с PARSING + pivot

но всеравно будет запутанее и медленнее 20980873

.....
stax
...
Рейтинг: 0 / 0
24.11.2017, 14:59
| Ответить | Цитировать | Написать  
Не работает подзапрос multiset и отбратно
    #39559087
Фотография barrabas
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
barrabas
Участник
Staxbarrabasвот ситуация с multiset как-то напрягла, я помню использовал его давно в некоторых проектах, вот и думаю, правильно ли там работает
в оракаля where rownum = 3 не прокатит

получается надо обратно во вьюшку

имхо
можно строку распарсить (regular, substr, хмл ...) в таблицу и полученное соеденить с PARSING + pivot

но всеравно будет запутанее и медленнее 20980873

.....
stax
да это понятно, я сразу после Elic допер, что тормозил :),
там же в другом херня


Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
with XXX as  
(select CODOGRAMM,
         cast(multiset(select regexp_substr(k.CODOGRAMM,
                            '[^~]+', 1, rownum) 
                      from dual           
                  connect by rownum <= 30) as SYS.Odcivarchar2list) CODES_TBL
   from (select 1 ID_RECORD_TYPE, '1~2~3' CODOGRAMM
           from DUAL
          union all 
         select 1 ID_RECORD_TYPE, '4~5~6' CODOGRAMM
           from DUAL           
        ) k
)
select  CODOGRAMM, (select max(COLUMN_VALUE) from table(cast(CODES_TBL as SYS.Odcivarchar2list))) MAX_vAL 
  from  XXX



выводит
CODOGRAMMMAX_VAL1~2~334~5~6 3
...
Рейтинг: 0 / 0
24.11.2017, 15:24
| Ответить | Цитировать | Написать  
Не работает подзапрос multiset и отбратно
    #39559128
Работавокруг, да около
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Работавокруг, да около
Гость
barrabasтам же в другом херняоптимизатор не понял, что есть корреляция и закешировал результат подзапроса. Добавь на верхний уровень подзапроса ссылку на наружний запрос по типу (select ... from table(...) innerQ where outerQ.codogramm is not null).
...
Рейтинг: 0 / 0
24.11.2017, 16:26
| Ответить | Цитировать | Написать  
Не работает подзапрос multiset и отбратно
    #39559138
Материализд
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Материализд
Гость
Работавокруг, да около,

Ну или так:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
SQL> with XXX as
  2  (select CODOGRAMM,
  3           cast(multiset(select regexp_substr(k.CODOGRAMM,
  4                              '[^~]+', 1, rownum)
  5                        from dual
  6                    connect by rownum <= 30) as SYS.Odcivarchar2list) CODES_TBL
  7     from (select 1 ID_RECORD_TYPE, '1~2~3' CODOGRAMM
  8             from DUAL
  9            union all
 10           select 1 ID_RECORD_TYPE, '4~5~6' CODOGRAMM
 11             from DUAL
 12          ) k
 13  )
 14  select  CODOGRAMM, (select max(COLUMN_VALUE) from table(cast(z.CODES_TBL as SYS.Odcivarchar2list))) MAX_vAL
 15    from  XXX z;

CODOG MAX_VAL
----- ----------
1~2~3 3
4~5~6 3

SQL> 2
  2* (select CODOGRAMM,
SQL> c/select /select rownum, /
  2* (select rownum, CODOGRAMM,
SQL> /

CODOG MAX_VAL
----- ----------
1~2~3 3
4~5~6 6
...
Рейтинг: 0 / 0
24.11.2017, 16:38
| Ответить | Цитировать | Написать  
Не работает подзапрос multiset и отбратно
    #39559142
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
barrabasStaxпропущено...

в оракаля where rownum = 3 не прокатит

получается надо обратно во вьюшку

имхо
можно строку распарсить (regular, substr, хмл ...) в таблицу и полученное соеденить с PARSING + pivot

но всеравно будет запутанее и медленнее 20980873

.....
stax
да это понятно, я сразу после Elic допер, что тормозил :),
там же в другом херня


Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
with XXX as  
(select CODOGRAMM,
         cast(multiset(select regexp_substr(k.CODOGRAMM,
                            '[^~]+', 1, rownum) 
                      from dual           
                  connect by rownum <= 30) as SYS.Odcivarchar2list) CODES_TBL
   from (select 1 ID_RECORD_TYPE, '1~2~3' CODOGRAMM
           from DUAL
          union all 
         select 1 ID_RECORD_TYPE, '4~5~6' CODOGRAMM
           from DUAL           
        ) k
)
select  CODOGRAMM, (select max(COLUMN_VALUE) from table(cast(CODES_TBL as SYS.Odcivarchar2list))) MAX_vAL 
  from  XXX



выводит
CODOGRAMMMAX_VAL1~2~334~5~6 3

херня какая-то
пятница, перегрелся наверное я

если не баг, не могу понять почему так

.....
stax
...
Рейтинг: 0 / 0
24.11.2017, 16:43
| Ответить | Цитировать | Написать  
Не работает подзапрос multiset и отбратно
    #39559143
K790
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
K790
Участник
Код: plsql
1.
from table(cast(z.CODES_TBL as SYS.Odcivarchar2list))


не совсем понял, зачем кастовать закастованное?
...
Рейтинг: 0 / 0
24.11.2017, 16:43
| Ответить | Цитировать | Написать  
Не работает подзапрос multiset и отбратно
    #39559145
Фотография barrabas
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
barrabas
Участник
МатериализдРаботавокруг, да около,

Ну или так:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
SQL> with XXX as
  2  (select CODOGRAMM,
  3           cast(multiset(select regexp_substr(k.CODOGRAMM,
  4                              '[^~]+', 1, rownum)
  5                        from dual
  6                    connect by rownum <= 30) as SYS.Odcivarchar2list) CODES_TBL
  7     from (select 1 ID_RECORD_TYPE, '1~2~3' CODOGRAMM
  8             from DUAL
  9            union all
 10           select 1 ID_RECORD_TYPE, '4~5~6' CODOGRAMM
 11             from DUAL
 12          ) k
 13  )
 14  select  CODOGRAMM, (select max(COLUMN_VALUE) from table(cast(z.CODES_TBL as SYS.Odcivarchar2list))) MAX_vAL
 15    from  XXX z;

CODOG MAX_VAL
----- ----------
1~2~3 3
4~5~6 3

SQL> 2
  2* (select CODOGRAMM,
SQL> c/select /select rownum, /
  2* (select rownum, CODOGRAMM,
SQL> /

CODOG MAX_VAL
----- ----------
1~2~3 3
4~5~6 6


отлично. спасибо.
...
Рейтинг: 0 / 0
24.11.2017, 16:44
| Ответить | Цитировать | Написать  
Не работает подзапрос multiset и отбратно
    #39559148
Фотография barrabas
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
barrabas
Участник
K790
Код: plsql
1.
from table(cast(z.CODES_TBL as SYS.Odcivarchar2list))


не совсем понял, зачем кастовать закастованное?
привычка, т.к. иначе в EM не работал анализатор плана если в запросе был просто table(XXX), раньше во всяком случае.
...
Рейтинг: 0 / 0
24.11.2017, 16:46
| Ответить | Цитировать | Написать  
28 сообщений из 28, показаны все 2 страниц
  все  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Не работает подзапрос multiset и отбратно
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?all=1&fid=52&tid=1884853]:
 0ms
get settings:
 8ms
get forum list:
 14ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 133ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 69ms
get tp. blocked users:
 2ms
others: 233ms
total:  473ms
созд. / загр.: 473ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]