Simplification de requête

**Louis-Guillaume Morand** · 28/02/2008, 14h57

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.

**orafrance** · 28/02/2008, 15h18

pourquoi pas une vue sur :

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
              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)
                      );

Sinon, un truc de ce style :

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
stmt := '               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) ' ||
        '              ) ';
 
IF (p_date = 'Between')
THEN
	  stmt := stmt || '  AND CALL.alert_date > p_date1 AND CALL.alert_date < p_date2');
ELSIF (p_date IS NOT NULL)
	  stmt := stmt || ' AND CALL.alert_date ' || p_date || ' p_date1';
END IF;
 
FOR i IN stmt LOOP ...

ORA-24338

tu ouvres un curseur qui n'existe pas... c'est plutôt FOR...LOOP que tu dois faire je pense.

**Louis-Guillaume Morand** · 28/02/2008, 15h30

pour la vue...bah je sais pas faire

pour ta solution du statement que tu construis, j'y ai pensé mais justement, je vois pas trop la fin après

FOR i IN stmt LOOP ...

en fait, en oracle (et j'aime pas pour ca), pour l'interroger depuis du .Net, tu dois passer un curseur vide, et faire "open cur for select" et ca se retourne tout seul.
pour l'erreur que tu cites, justement je ne la comprend pas, je n'ai jamais eu besoin de créer le curseur. dans des centaines d'autres sp, ca marche directement.

venant du tsql, le plsql me perd un peu dans sa logique là :/
Il faudrait que j'arrive à remplir mon cursor depuis mon statement.

la solution de la vue facilement la lecture du code mais j'aurais tjs mon erreur et la deuxieme solution me semble pas mal mais je vois pas comment l'utiliser :'(

**orafrance** · 28/02/2008, 15h34

Envoyé par Louis-Guillaume Morand

pour la vue...bah je sais pas faire

Rien de plus simple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CREATE VIEW tavue AS [ta requête];

Envoyé par Louis-Guillaume Morand

pour ta solution du statement que tu construis, j'y ai pensé mais justement, je vois pas trop la fin après

j'comprends pas ?

Envoyé par Louis-Guillaume Morand

en fait, en oracle (et j'aime pas pour ca), pour l'interroger depuis du .Net, tu dois passer un curseur vide, et faire "open cur for select" et ca se retourne tout seul.
pour l'erreur que tu cites, justement je ne la comprend pas, je n'ai jamais eu besoin de créer le curseur. dans des centaines d'autres sp, ca marche directement.

c'est un code .Net que tu donnes là ?

Envoyé par Louis-Guillaume Morand

la solution de la vue facilement la lecture du code mais j'aurais tjs mon erreur et la deuxieme solution me semble pas mal mais je vois pas comment l'utiliser :'(

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

OPEN CURSOR cur FOR stmt

non ?

**Louis-Guillaume Morand** · 28/02/2008, 15h40

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
   PROCEDURE sp_basicsearch (
      cur       OUT      sys_refcursor,
      p_book    IN       NVARCHAR2,
      p_cty     IN       NVARCHAR2,
      p_obj     IN       NVARCHAR2,
      p_prd     IN       NVARCHAR2,
      p_ref     IN       NVARCHAR2,
      p_file    IN       NUMBER,
      p_date    IN       NVARCHAR2,
      p_date1   IN       DATE DEFAULT sysdate,
     p_date2   IN       DATE DEFAULT sysdate
   )
   IS
 
	  stmt NVARCHAR2(500);
   BEGIN
 
     stmt := '               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) ' ||
        '              ) ';
 
		IF (p_date = 'Between')
		THEN
			  stmt := stmt || '  AND CALL.alert_date > p_date1 AND CALL.alert_date < p_date2';
 
		ELSIF (p_date IS NOT NULL)
			  stmt := stmt || ' AND CALL.alert_date ' || p_date || ' p_date1';
		END IF;
 
	    open cur for stmt;
   END;
END pkg_reports;
/

plusieurs choses (mais on approche ^^)

au spell il aime pas cette ligne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
ELSIF (p_date IS NOT NULL)
			  stmt := stmt || ' AND CALL.alert_date ' || p_date || ' p_date1';

=>

(S92) Expecting: ! != (+) * ** + - / < <= <> || = = > >= ^ ^= AND AT BETWEEN DAY IN IS LIKE LIKE2 LIKE4 LIKEC MOD NOT OR REM THEN YEAR

edit: en fait c'est le THEN qui manque

à la compilation, il aime pas celle là

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

open cur for stmt;

=>

PLS-00382: expression is of wrong type

j'ai trouvé un open...for execute
http://www.redhat.com/docs/manuals/d...l-cursors.html
mais oracle n'en veut pas car ca semble être pour postgres uniquement

**mnitu** · 28/02/2008, 16h04

Coller le paramètres de la procédure dans la requête comme c'est proposé ne marchéra pas!

Simplification de requête

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager