Simplification de requête
Voici ma requête actuelle
Code:
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
| IF (p_date = '')
THEN
BEGIN
OPEN cur FOR
SELECT *
FROM t_alerts a, t_alert_base base, t_alert_call CALL
WHERE a.pk_alert = base.pk_alert
AND CALL.pk_option = base.pk_option
AND (p_obj IS NULL OR CALL.option_object = p_obj)
AND (p_prd IS NULL OR CALL.product = p_prd)
AND (p_ref IS NULL OR base.option_ref = p_ref)
AND (p_file IS NULL OR a.pk_file = p_file)
AND (p_book IS NULL OR CALL.book IN (SELECT pk_book
FROM t_book
WHERE lbl = p_book))
AND ( p_cty IS NULL
OR CALL.counterparty IN (SELECT pk_counterparty
FROM t_counterparty
WHERE lbl = p_cty)
);
END;
ELSIF (p_date = '=')
THEN
BEGIN
OPEN cur FOR
SELECT *
FROM t_alerts a, t_alert_base base, t_alert_call CALL
WHERE a.pk_alert = base.pk_alert
AND CALL.pk_option = base.pk_option
AND (p_obj IS NULL OR CALL.option_object = p_obj)
AND (p_prd IS NULL OR CALL.product = p_prd)
AND (p_ref IS NULL OR base.option_ref = p_ref)
AND (p_file IS NULL OR a.pk_file = p_file)
AND (p_book IS NULL OR CALL.book IN (SELECT pk_book
FROM t_book
WHERE lbl = p_book))
AND ( p_cty IS NULL
OR CALL.counterparty IN (SELECT pk_counterparty
FROM t_counterparty
WHERE lbl = p_cty)
)
AND CALL.alert_date = p_date1;
END;
ELSIF (p_date = '<')
THEN
BEGIN
OPEN cur FOR
SELECT *
FROM t_alerts a, t_alert_base base, t_alert_call CALL
WHERE a.pk_alert = base.pk_alert
AND CALL.pk_option = base.pk_option
AND (p_obj IS NULL OR CALL.option_object = p_obj)
AND (p_prd IS NULL OR CALL.product = p_prd)
AND (p_ref IS NULL OR base.option_ref = p_ref)
AND (p_file IS NULL OR a.pk_file = p_file)
AND (p_book IS NULL OR CALL.book IN (SELECT pk_book
FROM t_book
WHERE lbl = p_book))
AND ( p_cty IS NULL
OR CALL.counterparty IN (SELECT pk_counterparty
FROM t_counterparty
WHERE lbl = p_cty)
)
AND CALL.alert_date < p_date1;
END;
ELSIF (p_date = '>')
THEN
BEGIN
OPEN cur FOR
SELECT *
FROM t_alerts a, t_alert_base base, t_alert_call CALL
WHERE a.pk_alert = base.pk_alert
AND CALL.pk_option = base.pk_option
AND (p_obj IS NULL OR CALL.option_object = p_obj)
AND (p_prd IS NULL OR CALL.product = p_prd)
AND (p_ref IS NULL OR base.option_ref = p_ref)
AND (p_file IS NULL OR a.pk_file = p_file)
AND (p_book IS NULL OR CALL.book IN (SELECT pk_book
FROM t_book
WHERE lbl = p_book))
AND ( p_cty IS NULL
OR CALL.counterparty IN (SELECT pk_counterparty
FROM t_counterparty
WHERE lbl = p_cty)
)
AND CALL.alert_date > p_date1;
END;
ELSIF (p_date = 'Between')
THEN
BEGIN
OPEN cur FOR
SELECT *
FROM t_alerts a, t_alert_base base, t_alert_call CALL
WHERE a.pk_alert = base.pk_alert
AND CALL.pk_option = base.pk_option
AND (p_obj IS NULL OR CALL.option_object = p_obj)
AND (p_prd IS NULL OR CALL.product = p_prd)
AND (p_ref IS NULL OR base.option_ref = p_ref)
AND (p_file IS NULL OR a.pk_file = p_file)
AND (p_book IS NULL OR CALL.book IN (SELECT pk_book
FROM t_book
WHERE lbl = p_book))
AND ( p_cty IS NULL
OR CALL.counterparty IN (SELECT pk_counterparty
FROM t_counterparty
WHERE lbl = p_cty)
)
AND CALL.alert_date > p_date1
AND CALL.alert_date < p_date2;
END;
END IF; |
si on regarde bien, dans chaque bloc, une partie est commune
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| OPEN cur FOR
SELECT *
FROM t_alerts a, t_alert_base base, t_alert_call CALL
WHERE a.pk_alert = base.pk_alert
AND CALL.pk_option = base.pk_option
AND (p_obj IS NULL OR CALL.option_object = p_obj)
AND (p_prd IS NULL OR CALL.product = p_prd)
AND (p_ref IS NULL OR base.option_ref = p_ref)
AND (p_file IS NULL OR a.pk_file = p_file)
AND (p_book IS NULL OR CALL.book IN (SELECT pk_book
FROM t_book
WHERE lbl = p_book))
AND ( p_cty IS NULL
OR CALL.counterparty IN (SELECT pk_counterparty
FROM t_counterparty
WHERE lbl = p_cty)
) |
j'aimerai trouver un moyen "factoriser" tout cela. par exemple en mettant cette partie commune dans une table temporaire puis ensuite faire une select sur cette table selon les autres filtres (if, etc).
l'avantage: meilleure visibilité, et surtout un seul bloc de code à maintenir.
peut-être surtout que cela m'aidera à trouver l'origine de l'erreur "ORA-24338: statement handle not executed". je suppose que ca vient des cursor imbriqués dans un bloc IF mais je vois pas comment faire dedans.
merci d'avance à celui(ceux) qui pourra(ont) m'éclairer.
