Discussion :

[kaos189]

Salut a tous,

J'étudie actuellement le fonctionnement des vues et voici le point sur lequel
je bloque: je dois créer une vue pour effectuer un comptage mais le problème est qu'il n'est pas possible d'utiliser des fonctions comme GROUP BY, DISTINCT et ROWNUM...

Quelle serait donc le(s) alternative(s) pour y parvenir?

Voici plus précisément mon cas détaillé:

Travaille sur Oracle iSQL*Plus

Ma table DDL:

Code :

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

1
2
3
4
5
CREATE TABLE jobs_agents
(
agent_id NUMBER(4),
job_id NUMBER(4)
);

Puis quelques valeurs pour l'exemple:

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
INSERT INTO JOBS_AGENTS VALUES (1,6);
INSERT INTO JOBS_AGENTS VALUES (1,13);
INSERT INTO JOBS_AGENTS VALUES (1,17);
INSERT INTO JOBS_AGENTS VALUES (2,3);
INSERT INTO JOBS_AGENTS VALUES (2,8);
INSERT INTO JOBS_AGENTS VALUES (2,9);
INSERT INTO JOBS_AGENTS VALUES (2,14);
INSERT INTO JOBS_AGENTS VALUES (3,15);
INSERT INTO JOBS_AGENTS VALUES (3,16);
INSERT INTO JOBS_AGENTS VALUES (3,22);
INSERT INTO JOBS_AGENTS VALUES (3,24);
INSERT INTO JOBS_AGENTS VALUES (4,1);
INSERT INTO JOBS_AGENTS VALUES (4,2);
INSERT INTO JOBS_AGENTS VALUES (5,7);
INSERT INTO JOBS_AGENTS VALUES (5,10);
INSERT INTO JOBS_AGENTS VALUES (6,23);
INSERT INTO JOBS_AGENTS VALUES (7,11);
INSERT INTO JOBS_AGENTS VALUES (7,17);
INSERT INTO JOBS_AGENTS VALUES (8,25);
INSERT INTO JOBS_AGENTS VALUES (9,4);
INSERT INTO JOBS_AGENTS VALUES (9,5);
INSERT INTO JOBS_AGENTS VALUES (10,12);
INSERT INTO JOBS_AGENTS VALUES (11,19);
INSERT INTO JOBS_AGENTS VALUES (11,20);
INSERT INTO JOBS_AGENTS VALUES (11,21);
INSERT INTO JOBS_AGENTS VALUES (11,26);

Maintenant l'information DML que je dois isoler:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT

agent_id,
COUNT(job_id)

FROM

jobs_agents

GROUP BY
agent_id

ORDER BY
agent_id;

Evidement, lorsque je cré la vue, celle-ci échoue fatalement à cause de la fonction GROUP BY:

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
CREATE VIEW jobs_per_agent
AS

(
SELECT
agent_id,
COUNT(job_id)

FROM
jobs_agents

GROUP BY
agent_id

ORDER BY
agent_id
);

Si quelqu'un a une solution, qu'il n'hésite pas!

Merci pour vos réponses. a+

[PpPool]

Merci d'indiquer la release d'oracle

si tu es au moins en 9i
les fonctions analytiques sont tes amis

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
SQL> CREATE VIEW jobs_per_agent
  2  AS ( SELECT DISTINCT(AGENT_ID) AS MyAgent,
  3  COUNT(JOB_ID) over(partition by AGENT_ID) AS MyCpt
  4  FROM JOBS_AGENTS);
 
Vue créée.
 
SQL> select * from jobs_per_agent;
 
   MYAGENT      MYCPT
---------- ----------
         1          3
         2          4
         3          4
         4          2
         5          2
         6          1
         7          2
         8          1
         9          2
        10          1
        11          4

[dragon74]

Tiens, c'est la première fois que j'entend cela. Et tu as quoi comme message d'erreur ??? Alias le count et oublies pas l'espace dans l'order by

Essayes ceci:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
CREATE VIEW jobs_per_agent
AS
(
SELECT
agent_id,
COUNT(job_id) count_job_id
FROM
jobs_agents
GROUP BY
agent_id
ORDER BY agent_id
);

[kaos189]

J'utilise la version 10g mais je ne suis pas supposé me servir de la fonction analytique car je ne l'ai malheureusement pas abordé au cours de mon séminaire...

Quand j'execute ton script Dagon74 j'ai toujours la même erreur, à savoir:

Code :

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

1
2
3
4
5
ORDER BY agent_id
*
 
ERREUR à la ligne 11 : 
ORA-00907: parenthèse de droite absente

??? C'est étonnant qu'elle n'apparaisse pas chez toi?

[Invité]

Ta syntaxe n'était pas correcte :

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
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
 
 
13:34:40 PROMPT> CREATE VIEW jobs_per_agent
13:34:52   2  AS SELECT
13:34:52   3  agent_id,
13:34:52   4  COUNT(job_id) COMPTE
13:34:52   5  FROM jobs_agents
13:34:52   6  GROUP BY
13:34:52   7  agent_id
13:34:52   8  ORDER BY
13:34:52   9  agent_id ;
 
View created.
 
 
13:35:15 PROMPT> select * from jobs_per_agent ;
 
  AGENT_ID     COMPTE
---------- ----------
         1          3
         2          4
         3          4
         4          2
         5          2
         6          1
         7          2
         8          1
         9          2
        10          1
        11          4
 
11 rows selected.
 
13:35:28 PROMPT> exit

[deadoralive]

effectivement il faut 'aliaser' les colonnes calculées

si on garde les parenthèses, il faut supprimer l'order by sinon sans ça passe, curieux. Les premières versions de Sql n'acceptaient pas l'order by dans les vues

[skuatamad]

En fait il faut que le order by soit en dehors des parenthèses :

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
 
SQL> CREATE OR REPLACE VIEW jobs_per_agent
AS
(
SELECT
agent_id,
COUNT(job_id) count_job_id
FROM
jobs_agents
GROUP BY
agent_id
)
ORDER BY agent_id
;  2    3    4    5    6    7    8    9   10   11   12   13
 
View created.

Par contre ça sert à quoi de faire un ORDER BY dans une vue.
En cas de jointure faudra bien retrié, ça fait donc double de boulot !