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