Voici ma requête actuelle
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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 : Sélectionner tout - Visualiser dans une fenêtre à part
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.