orawish,
допускаю, что замаскировано ...
более подробно:
система отчетов по трафику.
отчет формируется запросом:
заданы бинд-переменные
:l_Date_From :l_Date_To
все остальные переменные = null.
При динамическом формировании запроса добавляется условие AND (PHONEA LIKE '%111111111%' ESCAPE '\')
В результате иногда меняются местами данные в полях ORIGINATOR, TERMINATOR.
т.е. в двух отчетах с одним и тем же запросом:
IDCDR (уникальный ID CDR-а) одинаковый но значения ORIGINATOR, TERMINATOR строго "наоборот" ...
при этом
запись CDR-а так же гарантированно не меняется с момента ее insert-a.
справочник DIMTRANKGROUPS так же не меняется.
Запрос из вьюхи STAT_DETAIL_V (формирование данных отчета. В обоих случаях (правильно/неправильно)текст запроса один и тот же. лог подтверждает):
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
INSERT INTO STAT.RPT_R_DETAIL_CDR (
REPORT_ID, ANSTIME, I_TRUNK_GROUP_IN, I_TRUNK_GROUP_OUT, CONVERSATIONTIME, IDCDR, SOFTX, GATE,
CALLER_CATEG, INTELLI_TYPE,
PH_A_NATURE, PHONEA, PH_B_NATURE, PHONEB, PHONEDIAL,
MGMN_SIGN, ORIGINATOR, TERMINATOR,
CODE_TERM_HUAWEI, NAME_TERM_HUAWEI, NAMETERMREASON, CODE_TERM_Q, NAME_TERM_Q,
REGION_NAME, CODEPREFIXMIN, CODEPREFIXMAX,
PDD, SERVICE_TYPE, ADD_SERVICE,
PH_ORIG_NATURE, PHONEORIG, PH_REDIR_NATURE, PHONEREDIR, PH_CONN_NATURE, CONNECTED_NUMBER)
SELECT /*+ parallel(6) */ :p_Report_Id, ANSTIME, I_TRUNK_GROUP_IN, I_TRUNK_GROUP_OUT, CONVERSATIONTIME, IDCDR, SOFTX, GATE, CALLER_CATEG, INTELLI_TYPE, PH_A_NATURE, PHONEA, PH_B_NATURE, PHONEB, PHONEDIAL, MGMN_SIGN, ORIGINATOR, TERMINATOR, CODE_TERM_HUAWEI, NAME_TERM_HUAWEI, NAMETERMREASON, CODE_TERM_Q, NAME_TERM_Q, REGION_NAME, CODEPREFIXMIN, CODEPREFIXMAX, PDD, SERVICE_TYPE, ADD_SERVICE, PH_ORIG_NATURE, PHONEORIG, PH_REDIR_NATURE, PHONEREDIR, PH_CONN_NATURE, CONNECTED_NUMBER
FROM STAT_DETAIL_V v
WHERE v.anstime BETWEEN :l_Date_From AND :l_Date_To
AND (v.conversationtime >= :l_Dur_Min OR :l_Dur_Min IS NULL)
AND (v.conversationtime <= :l_Dur_Max OR :l_Dur_Max IS NULL)
AND (v.pdd >= :l_PDD_Min OR :l_PDD_Min IS NULL)
AND (v.pdd <= :l_PDD_Max OR :l_PDD_Max IS NULL)
AND (v.idnumbernaturea <= :l_Norm_A OR :l_Norm_A IS NULL)
AND (v.idnumbernatureb <= :l_Norm_B OR :l_Norm_B IS NULL)
AND (v.idnumbernatureorig <= :l_Norm_Orig OR :l_Norm_Orig IS NULL)
AND (v.idnumbernatureredir <= :l_Norm_Redir OR :l_Norm_Redir IS NULL)
AND (v.idnumbernatureconnected <= :l_Norm_Conn OR :l_Norm_Conn IS NULL)
AND (PHONEA LIKE '%111111111%' ESCAPE '\')
сама вьюха STAT_DETAIL_V:
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.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
CREATE OR REPLACE FORCE VIEW STAT_DETAIL_V
(
IDCDR,
IDSWITCH,
SOFTX,
GATE,
CALLER_CATEG,
INTELLI_TYPE,
ANSTIME,
PH_A_NATURE,
PHONEA,
PH_B_NATURE,
PHONEB,
PHONEDIAL,
MGMN_SIGN,
CONVERSATIONTIME,
ORIGINATOR,
TERMINATOR,
CODE_TERM_HUAWEI,
NAME_TERM_HUAWEI,
NAMETERMREASON,
CODE_TERM_Q,
NAME_TERM_Q,
REGION_NAME,
CODEPREFIXMIN,
CODEPREFIXMAX,
PDD,
I_TRUNK_GROUP_IN,
I_TRUNK_GROUP_OUT,
SERVICE_TYPE,
ADD_SERVICE,
PH_ORIG_NATURE,
PHONEORIG,
PH_REDIR_NATURE,
PHONEREDIR,
PH_CONN_NATURE,
CONNECTED_NUMBER,
IDCALLERCATEGORY,
IDTGA,
IDTGB,
IDTERM,
IDTERMREASON,
IDBEARERSERVICE,
IDSUPSERVTYPE,
IDNUMBERNATUREA,
IDNUMBERNATUREB,
IDNUMBERNATUREORIG,
IDNUMBERNATUREREDIR,
IDNUMBERNATURECONNECTED,
IDDIRECTION,
IDGATE
)
AS
SELECT f.idcdr,
f.idswitch,
TO_CHAR (ds.codeswitch) || ' ' || ds.nameswitch SoftX,
TO_CHAR (dsg.codeswitch) || ' ' || dsg.nameswitch Gate,
dc.namecallercategory Caller_Categ,
dis.nameiservice Intelli_Type,
f.anstime,
f.ph_a_nature,
f.phonea,
f.ph_b_nature,
f.phoneb,
f.phonedial,
f.isintername mgmn_sign,
conversationtime,
dtga.nametg originator,
dtgb.nametg terminator,
dth.codetermh code_term_huawei,
dth.nametermh name_term_huawei,
dr.nametermreason,
dth.codetermq code_term_q,
dth.nametermq name_term_q,
NVL (NVL (dd.namedirection, dd.namezone), dd.namecountry) region_name,
dd.codeprefixmin,
dd.codeprefixmax,
f.pdd,
f.i_trunk_group_in,
f.i_trunk_group_out,
db.namebearerservice service_type,
dsp.namesupservicetype Add_Service,
f.ph_orig_nature,
f.phoneorig,
f.ph_redir_nature,
f.phoneredir,
f.ph_conn_nature,
f.connected_number,
f.idcallercategory,
f.idtga,
f.idtgb,
f.idterm,
f.idtermreason,
f.idbearerservice,
F.idsupservtype,
f.idnumbernaturea,
f.idnumbernatureb,
f.idnumbernatureorig,
f.idnumbernatureredir,
f.idnumbernatureconnected,
f.iddirection,
f.idgate
FROM
fact_cdr f,
DIMSWITCHES ds,
DIMSWITCHES dsg,
DIMCALLERCATEGORIES dc,
DIMISERVICES dis,
DIMSUPSERVICETYPES dsp,
DIMTRANKGROUPS dtga,
DIMTRANKGROUPS dtgb,
DIMTERMINATIONCODES dth,
DIMTERMINATIONREASONS dr,
DIMDIRECTIONS dd,
DIMBEARERSERVICES db
WHERE
f.idswitch = ds.idswitch(+)
AND f.idgate = dsg.idswitch(+)
AND f.idcallercategory = dc.idcallercategory(+)
AND f.idiservice = dis.idiservice(+)
AND F.IDSUPSERVTYPE = dsp.IDSUPSERVICETYPE(+)
AND f.idtga = dtga.idtg(+)
AND f.idtgb = dtgb.idtg(+)
AND f.idterm = dth.idterm(+)
AND f.idtermreason = dr.idtermreason(+)
AND f.iddirection = dd.iddirection(+)
AND f.idbearerservice = db.idbearerservice(+)
|
