-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathselect_most_matching.sql
392 lines (375 loc) · 14 KB
/
select_most_matching.sql
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
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
/* Formatted on 2010/09/29 14:50 (Formatter Plus v4.8.7) */
/* Simple */
SELECT z.*,
DECODE (spn.special_number_id,
NULL, 'Not Special',
'Special: ' || spn.special_number_des
) special
FROM (SELECT *
FROM (SELECT zp.zpcode, zp.des, zp.DIGITS,
DECODE (pt.trdigits, NULL, 'No', 'Yes') translated
FROM MPDPTTAB pt,
MPUZPTAB zp,
(SELECT '+20' || TO_CHAR (117) num
FROM DUAL) dummy
WHERE '+' || SUBSTR (pt.ddigits(+), 3) = dummy.num
AND DECODE (pt.trdigits,
NULL, dummy.num,
'+' || SUBSTR (pt.trdigits, 3)
) LIKE zp.DIGITS || '%'
ORDER BY LENGTH (zp.DIGITS) DESC)
WHERE ROWNUM < 2) z,
SPECIAL_NUMBER spn
WHERE spn.zpcode(+) = z.zpcode AND spn.special_number_vsdate(+) <=
TRUNC (SYSDATE)
AND spn.special_number_status(+) = 'X';
SELECT /*+ ORDERED */
tmm.tmcode tm, tm.des rate_plan, tmm.spcode sp,
sp.des service_package, tmm.sncode sn, sn.des service, gvm.zncode zn,
zn.des tariff_zone, rim.ttcode tt, tt.des tariff_time,
rim.rate_type_id rt, rt.rate_type_des rate_type,
rppv.parameter_rownum rn, rppv.parameter_value_float / 1000 egp
FROM MPULKGVM gvm,
MPULKTMM tmm,
mputmtab tm,
MPUSPTAB sp,
MPUSNTAB sn,
MPULKRIM rim,
MPUZNTAB zn,
MPUTTTAB tt,
UDC_RATE_TYPE_TABLE rt,
RATE_PACK_ELEMENT rpe,
RATE_PACK_PARAMETER_VALUE rppv
WHERE gvm.zpcode = 9257
AND tm.tmcode = tmm.tmcode
AND tm.vscode = tmm.vscode
AND sp.spcode = tmm.spcode
AND sn.sncode = tmm.sncode
AND rim.ricode = tmm.ricode
AND rim.gvcode = gvm.gvcode
AND rim.gvvscode = gvm.vscode
AND rim.zncode = gvm.zncode
AND zn.zncode = gvm.zncode
AND tt.ttcode = rim.ttcode
AND rt.rate_type_id = rim.rate_type_id
AND rpe.rate_pack_entry_id = rim.rate_pack_entry_id
AND rppv.rate_pack_element_id = rpe.rate_pack_element_id
--AND tmm.sncode = 1
AND rim.vscode = (SELECT MAX (vscode)
FROM MPURIVSD
WHERE ricode = rim.ricode)
AND tmm.vscode = (SELECT MAX (vscode)
FROM mputmtab
WHERE tmcode = tmm.tmcode)
AND rppv.parameter_seqnum = 4
AND (rim.rate_type_id = 1 OR rppv.parameter_value_float <> 0)
ORDER BY tmm.tmcode,
tmm.sncode,
tmm.spcode,
rim.ttcode,
rim.rate_type_id,
rppv.parameter_rownum;
SELECT /*+ ORDERED */
tmm.tmcode tm, tm.des rate_plan, tmm.spcode sp,
sp.des service_package, tmm.sncode sn, sn.des service, gvm.zncode zn,
zn.des tariff_zone, rim.ttcode tt, tt.des tariff_time,
rim.rate_type_id rt, rt.rate_type_des rate_type,
rppv.parameter_rownum rn, rppv.parameter_value_float / 1000 egp /*,
rim.rate_pack_entry_id, m.*,
DECODE (spn.special_number_id, NULL, 'N', 'Y') spec*/
FROM (SELECT *
FROM (SELECT zp.zpcode, zp.des, zp.DIGITS
FROM MPDPTTAB pt,
MPUZPTAB zp,
(SELECT '+20' || TO_CHAR (0) num
FROM DUAL) dummy
WHERE '+' || SUBSTR (pt.ddigits(+), 3) = dummy.num
AND DECODE (pt.trdigits,
NULL, dummy.num,
'+' || SUBSTR (pt.trdigits, 3)
) LIKE zp.DIGITS || '%'
ORDER BY LENGTH (zp.DIGITS) DESC)
WHERE ROWNUM < 2) m,
SPECIAL_NUMBER spn,
MPULKGVM gvm,
MPULKTMM tmm,
mputmtab tm,
MPUSPTAB sp,
MPUSNTAB sn,
MPULKRIM rim,
MPUZNTAB zn,
MPUTTTAB tt,
UDC_RATE_TYPE_TABLE rt,
RATE_PACK_ELEMENT rpe,
RATE_PACK_PARAMETER_VALUE rppv
WHERE spn.zpcode(+) = m.zpcode
AND gvm.zpcode = m.zpcode
AND tm.tmcode = tmm.tmcode
AND tm.vscode = tmm.vscode
AND sp.spcode = tmm.spcode
AND sn.sncode = tmm.sncode
AND rim.ricode = tmm.ricode
AND rim.gvcode = gvm.gvcode
AND rim.gvvscode = gvm.vscode
AND rim.zncode = gvm.zncode
AND zn.zncode = gvm.zncode
AND tt.ttcode = rim.ttcode
AND rt.rate_type_id = rim.rate_type_id
AND rpe.rate_pack_entry_id = rim.rate_pack_entry_id
AND rppv.rate_pack_element_id = rpe.rate_pack_element_id
AND spn.special_number_vsdate(+) <= TRUNC (SYSDATE)
AND spn.special_number_status(+) = 'X'
AND tmm.tmcode = 84
AND rim.vscode = (SELECT MAX (vscode)
FROM MPURIVSD
WHERE ricode = rim.ricode)
AND tmm.vscode = (SELECT MAX (vscode)
FROM mputmtab
WHERE tmcode = tmm.tmcode)
AND rppv.parameter_seqnum = 4
AND (rim.rate_type_id = 1 OR rppv.parameter_value_float <> 0)
ORDER BY tmm.tmcode,
tmm.sncode,
tmm.spcode,
rim.ttcode,
rim.rate_type_id,
rppv.parameter_rownum;
DECLARE
v_num VARCHAR2 (30) := '1195';
v_tmcode INTEGER := 84;
v_zpcode INTEGER;
v_is_special NUMBER;
CURSOR c_all
IS
SELECT /*+ ORDERED */
tmm.tmcode tm, tm.des rate_plan, tmm.spcode sp,
sp.des service_package, tmm.sncode sn, sn.des service,
gvm.zncode zn, zn.des tariff_zone, rim.ttcode tt,
tt.des tariff_time, rim.rate_type_id rt,
rt.rate_type_des rate_type, rppv.parameter_rownum rn,
rppv.parameter_value_float / 1000 egp
FROM MPULKGVM gvm,
MPULKTMM tmm,
mputmtab tm,
MPUSPTAB sp,
MPUSNTAB sn,
MPULKRIM rim,
MPUZNTAB zn,
MPUTTTAB tt,
UDC_RATE_TYPE_TABLE rt,
RATE_PACK_ELEMENT rpe,
RATE_PACK_PARAMETER_VALUE rppv
WHERE gvm.zpcode = v_zpcode
AND tmm.tmcode = v_tmcode
AND tm.tmcode = tmm.tmcode
AND tm.vscode = tmm.vscode
AND sp.spcode = tmm.spcode
AND sn.sncode = tmm.sncode
AND rim.ricode = tmm.ricode
AND rim.gvcode = gvm.gvcode
AND rim.gvvscode = gvm.vscode
AND rim.zncode = gvm.zncode
AND zn.zncode = gvm.zncode
AND tt.ttcode = rim.ttcode
AND rt.rate_type_id = rim.rate_type_id
AND rpe.rate_pack_entry_id = rim.rate_pack_entry_id
AND rppv.rate_pack_element_id = rpe.rate_pack_element_id
AND rim.vscode = (SELECT MAX (vscode)
FROM MPURIVSD
WHERE ricode = rim.ricode)
AND tmm.vscode = (SELECT MAX (vscode)
FROM mputmtab
WHERE tmcode = tmm.tmcode)
AND rppv.parameter_seqnum = 4
AND (rim.rate_type_id = 1 OR rppv.parameter_value_float <> 0)
ORDER BY tmm.tmcode,
tmm.sncode,
tmm.spcode,
rim.ttcode,
rim.rate_type_id,
rppv.parameter_rownum;
BEGIN
SELECT zpcode
INTO v_zpcode
FROM (SELECT zp.zpcode, zp.des, zp.DIGITS
FROM MPDPTTAB pt,
MPUZPTAB zp,
(SELECT '+20' || v_num num
FROM DUAL) dummy
WHERE '+' || SUBSTR (pt.ddigits(+), 3) = dummy.num
AND DECODE (pt.trdigits,
NULL, dummy.num,
'+' || SUBSTR (pt.trdigits, 3)
) LIKE zp.DIGITS || '%'
ORDER BY LENGTH (zp.DIGITS) DESC)
WHERE ROWNUM < 2;
SELECT COUNT (*)
INTO v_is_special
FROM SPECIAL_NUMBER spn
WHERE spn.zpcode = v_zpcode
AND spn.special_number_vsdate <= TRUNC (SYSDATE)
AND spn.special_number_status = 'X';
DBMS_OUTPUT.PUT_LINE
('TM;RATE_PLAN;SP;SERVICE_PACKAGE;SN;SERVICE;ZN;TARIFF_ZONE;TT;TARIFF_TIME;RT;RATE_TYPE;RN;EGP'
);
IF v_is_special = 0
THEN
FOR r_all IN c_all
LOOP
DBMS_OUTPUT.PUT_LINE ( r_all.tm
|| ';'
|| r_all.rate_plan
|| ';'
|| r_all.sp
|| ';'
|| r_all.service_package
|| ';'
|| r_all.sn
|| ';'
|| r_all.service
|| ';'
|| r_all.zn
|| ';'
|| r_all.tariff_zone
|| ';'
|| r_all.tt
|| ';'
|| r_all.tariff_time
|| ';'
|| r_all.rt
|| ';'
|| r_all.rate_type
|| ';'
|| r_all.rn
|| ';'
|| r_all.egp
);
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE ('*;*;*;*;*;*;*;*;*;*;*;*;*;0');
END IF;
NULL;
END;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE
v_num VARCHAR2 (30) := '1195';
v_tmcode INTEGER := 84;
v_zpcode INTEGER;
v_is_special NUMBER;
v_line VARCHAR2 (3000);
CURSOR c_rpe_id
IS
SELECT /*+ ORDERED */
tmm.tmcode tm, tm.des rate_plan, tmm.spcode sp,
sp.des service_package, tmm.sncode sn, sn.des service,
gvm.zncode zn, zn.des tariff_zone, rim.ttcode tt,
tt.des tariff_time, rim.rate_type_id rt,
rt.rate_type_des rate_type, rim.rate_pack_entry_id
FROM MPULKGVM gvm,
MPULKTMM tmm,
mputmtab tm,
MPUSPTAB sp,
MPUSNTAB sn,
MPULKRIM rim,
MPUZNTAB zn,
MPUTTTAB tt,
UDC_RATE_TYPE_TABLE rt
WHERE gvm.zpcode = v_zpcode
AND tmm.tmcode = v_tmcode
AND tm.tmcode = tmm.tmcode
AND tm.vscode = tmm.vscode
AND sp.spcode = tmm.spcode
AND sn.sncode = tmm.sncode
AND rim.ricode = tmm.ricode
AND rim.gvcode = gvm.gvcode
AND rim.gvvscode = gvm.vscode
AND rim.zncode = gvm.zncode
AND zn.zncode = gvm.zncode
AND tt.ttcode = rim.ttcode
AND rt.rate_type_id = rim.rate_type_id
AND rim.vscode = (SELECT MAX (vscode)
FROM MPURIVSD
WHERE ricode = rim.ricode)
AND tmm.vscode = (SELECT MAX (vscode)
FROM mputmtab
WHERE tmcode = tmm.tmcode)
ORDER BY tmm.tmcode,
tmm.sncode,
tmm.spcode,
rim.ttcode,
rim.rate_type_id;
BEGIN
SELECT zpcode
INTO v_zpcode
FROM (SELECT zp.zpcode, zp.des, zp.DIGITS
FROM MPDPTTAB pt,
MPUZPTAB zp,
(SELECT '+20' || v_num num
FROM DUAL) dummy
WHERE '+' || SUBSTR (pt.ddigits(+), 3) = dummy.num
AND DECODE (pt.trdigits,
NULL, dummy.num,
'+' || SUBSTR (pt.trdigits, 3)
) LIKE zp.DIGITS || '%'
ORDER BY LENGTH (zp.DIGITS) DESC)
WHERE ROWNUM < 2;
SELECT COUNT (*)
INTO v_is_special
FROM SPECIAL_NUMBER spn
WHERE spn.zpcode = v_zpcode
AND spn.special_number_vsdate <= TRUNC (SYSDATE)
AND spn.special_number_status = 'X';
DBMS_OUTPUT.PUT_LINE
('TM;RATE_PLAN;SP;SERVICE_PACKAGE;SN;SERVICE;ZN;TARIFF_ZONE;TT;TARIFF_TIME;RT;RATE_TYPE;RN;EGP'
);
IF v_is_special = 0
THEN
FOR r_rpe_id IN c_rpe_id
LOOP
v_line :=
r_rpe_id.tm
|| ';'
|| r_rpe_id.rate_plan
|| ';'
|| r_rpe_id.sp
|| ';'
|| r_rpe_id.service_package
|| ';'
|| r_rpe_id.sn
|| ';'
|| r_rpe_id.service
|| ';'
|| r_rpe_id.zn
|| ';'
|| r_rpe_id.tariff_zone
|| ';'
|| r_rpe_id.tt
|| ';'
|| r_rpe_id.tariff_time
|| ';'
|| r_rpe_id.rt
|| ';'
|| r_rpe_id.rate_type
|| ';';
SELECT DISTINCT rppv.parameter_value_float INTO v_count FROM RATE_PACK_ELEMENT rpe,
RATE_PACK_PARAMETER_VALUE rppv
WHERE rpe.rate_pack_entry_id =
r_rpe_id.rate_pack_entry_id
AND rppv.rate_pack_element_id =
rpe.rate_pack_element_id
AND rppv.parameter_seqnum = 4
AND ((rppv.parameter_rownum = 2 AND rppv.parameter_value_float <> 0) OR rppv.parameter_rownum <> 2)
ORDER BY rppv.parameter_rownum;
IF v_count := 3
FOR r_egp IN ()
LOOP
NULL;
END LOOP;
v_line :=
v_line
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE ('*;*;*;*;*;*;*;*;*;*;*;*;*;0');
END IF;
NULL;
END;