ORA-00937 avec un sous select et une jointure

[apad]

Bonjour

j'utilise des fonctions analytiques et dans un cas particulier, je reçois le message d'erreur oracle ORA-00937 : la fonction de groupe ne porte pas sur un groupe simple
J'obtiens ce message lorsque j'utilise la fonction analytique dans un sous-select avec jointure.
Voici le code du sous select :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
  T0.C0 IDSOCIETE,
  MIN (T0.C1) OVER (PARTITION BY T0.C0) NBANOMALIE
FROM 
  (
    SELECT 
      LAA.IDSOCIETE C0,
      COUNT (DISTINCT LAA.PK) C1
    FROM 
      LISTE_ANOMALIE_ARTICLE LAA
    GROUP BY LAA.IDSOCIETE
  ) T0

Cette requête fonctionne parfaitement bien.

Maintenant si je l'utilise avec une jointure :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT 
  S.LIBSOCIETE,
  ANO.IDSOCIETE,
  ANO.NBANOMALIE
FROM 
  DMV_SOCIETE S,
  (
    SELECT
      T0.C0 IDSOCIETE,
      MIN (T0.C1) OVER (PARTITION BY T0.C0) NBANOMALIE
    FROM 
      (
        SELECT 
          LAA.IDSOCIETE C0,
          COUNT (DISTINCT LAA.PK) C1
        FROM 
          LISTE_ANOMALIE_ARTICLE LAA
        GROUP BY LAA.IDSOCIETE
      ) T0
  ) ANO
WHERE S.IDSOCIETE = ANO.IDSOCIETE

J'obtiens le message ORA-00937.

Je ne comprends pas pourquoi la clause de jointure (en rouge) provoque cette erreur. Si je retire la clause de jointure, j'obtiens évidemment un produit cartésien mais je n'ai plus d'erreur.

N.B. : Pas la peine de me préciser que la requête est inutilement compliquée et que j'aurais pu obtenir un résultat avec une requête beaucoup plus simple, je le sais déjà. Mais cette requête est générée en automatique par un outil BI et je n'ai pas de moyen pour la simplifier.

Le vrai problème est pourquoi le sous-select fonctionne-t-il seul et génère un message ORA-00937 lorsqu'il est utilisé avec une clause de jointure.
Voici de quoi recréer un petit jeu de test. Ma base est en 11g. Si quelqu'un peut déjà me dire si l'erreur se reproduit aussi sur sa base ...

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
CREATE TABLE LISTE_ANOMALIE_ARTICLE
(
  PK          VARCHAR2(44 CHAR) NOT NULL,
  PK_ARTICLE  VARCHAR2(23 CHAR),
  PK_PRODUIT  VARCHAR2(23 CHAR),
  CODE        NUMBER(15),
  IDSOCIETE   VARCHAR2(2 CHAR),
  IDARTICLE   VARCHAR2(20 CHAR),
  IDPRODUIT   VARCHAR2(20 CHAR)
);
INSERT INTO LISTE_ANOMALIE_ARTICLE VALUES ('1-01-000002520201', '01-000002520201', '01-00000252', 1, '01', '000002520201', '00000252');
INSERT INTO LISTE_ANOMALIE_ARTICLE VALUES ('1-01-000002520214', '01-000002520214', '01-00000252', 1, '01', '000002520214', '00000252');
INSERT INTO LISTE_ANOMALIE_ARTICLE VALUES ('1-01-000002520301', '01-000002520301', '01-00000252', 1, '01', '000002520301', '00000252');
INSERT INTO LISTE_ANOMALIE_ARTICLE VALUES ('1-01-000002520314', '01-000002520314', '01-00000252', 1, '01', '000002520314', '00000252');
INSERT INTO LISTE_ANOMALIE_ARTICLE VALUES ('1-02-000002520201', '02-000002520201', '02-00000252', 1, '02', '000002520201', '00000252');
INSERT INTO LISTE_ANOMALIE_ARTICLE VALUES ('1-02-000002520214', '02-000002520214', '02-00000252', 1, '02', '000002520214', '00000252');
INSERT INTO LISTE_ANOMALIE_ARTICLE VALUES ('1-02-000002520301', '02-000002520301', '02-00000252', 1, '02', '000002520301', '00000252');
INSERT INTO LISTE_ANOMALIE_ARTICLE VALUES ('1-02-000002520314', '02-000002520314', '02-00000252', 1, '02', '000002520314', '00000252');
INSERT INTO LISTE_ANOMALIE_ARTICLE VALUES ('1-08-ALY001', '08-ALY001', '08-ALY001', 1, '08', 'ALY001', 'ALY001');
INSERT INTO LISTE_ANOMALIE_ARTICLE VALUES ('1-17-0HUMIST40202H', '17-0HUMIST40202H', '17-0HUMIST4', 1, '17', '0HUMIST40202H', '0HUMIST4');
INSERT INTO LISTE_ANOMALIE_ARTICLE VALUES ('1-17-0HUMIST40214', '17-0HUMIST40214', '17-0HUMIST4', 1, '17', '0HUMIST40214', '0HUMIST4');
INSERT INTO LISTE_ANOMALIE_ARTICLE VALUES ('1-1R-00004', '1R-00004', '1R-00076', 1, '1R', '00004', '00076');
INSERT INTO LISTE_ANOMALIE_ARTICLE VALUES ('1-1R-00011', '1R-00011', '1R-00078', 1, '1R', '00011', '00078');
INSERT INTO LISTE_ANOMALIE_ARTICLE VALUES ('1-1R-00076', '1R-00076', '1R-00076', 1, '1R', '00076', '00076');
INSERT INTO LISTE_ANOMALIE_ARTICLE VALUES ('1-1R-00078', '1R-00078', '1R-00078', 1, '1R', '00078', '00078');
COMMIT;
 
CREATE TABLE DMV_SOCIETE
(
  IDSOCIETE          VARCHAR2(2 CHAR),
  LIBSOCIETE         VARCHAR2(30 CHAR)
);
 
INSERT INTO DMV_SOCIETE VALUES ('01', 'SOCIETE 01');
INSERT INTO DMV_SOCIETE VALUES ('02', 'SOCIETE 02');
INSERT INTO DMV_SOCIETE VALUES ('08', 'SOCIETE 08');
INSERT INTO DMV_SOCIETE VALUES ('17', 'SOCIETE 17');
INSERT INTO DMV_SOCIETE VALUES ('1R', 'SOCIETE 1R');
INSERT INTO DMV_SOCIETE VALUES ('1Z', 'SOCIETE 1Z');
COMMIT;

Cordialement

[mnitu]

C'est un bug de la 11g, probablement due aux transformations de la requête, ça marche en 10G

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
 
SQL> SELECT
  2    S.LIBSOCIETE,
  3    ANO.IDSOCIETE,
  4    ANO.NBANOMALIE
  5  FROM
  6    DMV_SOCIETE S,
  7    (
  8      SELECT
  9        T0.C0 IDSOCIETE,
 10        MIN (T0.C1) OVER (PARTITION BY T0.C0) NBANOMALIE
 11      FROM
 12        (
 13          SELECT
 14            LAA.IDSOCIETE C0,
 15            COUNT (DISTINCT LAA.PK) C1
 16          FROM
 17            LISTE_ANOMALIE_ARTICLE LAA
 18          GROUP BY LAA.IDSOCIETE
 19        ) T0
 20    ) ANO
 21  WHERE S.IDSOCIETE = ANO.IDSOCIETE
 22  /
 
LIBSOCIETE                     IDSOCIETE NBANOMALIE
------------------------------ --------- ----------
SOCIETE 01                     01                 4
SOCIETE 02                     02                 4
SOCIETE 08                     08                 1
SOCIETE 1R                     1R                 4
SOCIETE 17                     17                 2
 
SQL>

Cherchez la solution dans Oracle Support.

[laurentschneider]

probablement le bug 9004697, corrigé en 11.2.0.2

[apad]

Merci pour vos réponses précises et rapides.
Effectivement le bug 9004697 correspond bien au comportement que je rencontre. Ma base est en version 11.1.0.6.0, c'est donc une piste que je vais creuser.
Je fais installer le patch et je reviendrais vous dire si c'est résolu ou non.

Cordialement

[Waldar]

Attention, vous passerez alors de 11gR1 à 11gR2, je ne pense pas que ce soit considérable comme du patch !