ORA-00979: N'est pas une expression GROUP BY

Version imprimable

Voir 40 message(s) de cette discussion en une page

Bonjour,

Je ne vois dans la requête suivante ce qui ne va pas :
Code:

1 2 3 4 5 6 SELECT T1.COL1, SUM(T1.COL3), (SUM(T1.COL3) + (SELECT SUM(T2.COL2) FROM TABLE2 T2 WHERE T2.COL1 = T1.COL1 AND T2.COL2 = T1.COL2)) AS ALIAS1 FROM TABLE1 T1 WHERE T1.COL1 = VALEUR1 AND T1.COL2 = VALEUR2 GROUP BY T1.COL1
Lorsque je l'exécute, j'ai l'erreur suivante:

Citation:

ORA-00979: N'est pas une expression GROUP BY
00979. 00000 - "not a GROUP BY expression"
*Cause:
*Action:
Erreur à la ligne 1, colonne 136

Merci,

tu ne groupes pas sur t1.col2.

essaye :

Code:

1
2
3
4
5
6
7
8
9
10
  SELECT T1.COL1,
         SUM (T1.COL3),
         (  SUM (T1.COL3)
          + (SELECT SUM (T2.COL2)
               FROM TABLE2 T2
              WHERE T2.COL1 = T1.COL1 AND T2.COL2 = T1.COL2))
            AS ALIAS1
    FROM TABLE1 T1
   WHERE T1.COL1 = VALEUR1 AND T1.COL2 = VALEUR2
GROUP BY T1.COL1, T1.COL2;

Citation:
Envoyé par Jinkas
Bonjour,

Je ne vois dans la requête suivante ce qui ne va pas :
Code:

1 2 3 4 5 6 SELECT T1.COL1, SUM(T1.COL3), (SUM(T1.COL3) + (SELECT SUM(T2.COL2) FROM TABLE2 T2 WHERE T2.COL1 = T1.COL1 AND T2.COL2 = T1.COL2)) AS ALIAS1 FROM TABLE1 T1 WHERE T1.COL1 = VALEUR1 AND T1.COL2 = VALEUR2 GROUP BY T1.COL1
Lorsque je l'exécute, j'ai l'erreur suivante:

Merci,
Le group by statement doit être utilisé sur un champ spécifique et sur des fonctions de ce champ uniquement. Sinon tu as l'erreur 979 sous ORACLE.
ex :

Code:

select col1, col1*2, sum(col1) from tab1 group by col1;

sinon sur plusieurs champs :

Code:

select col1, col1*col2,col2, sum(col1) from tab1 group by col1,col2;

reprends la doc, c'est bien expliqué.

autrement, si tu ne veux pas grouper sur col2, alors tu supprimes la jointure sur col2

Code:

1
2
3
4
5
6
7
8
9
10
  SELECT T1.COL1,
         SUM (T1.COL3),
         (  SUM (T1.COL3)
          + (SELECT SUM (T2.COL2)
               FROM TABLE2 T2
              WHERE T2.COL1 = T1.COL1 ))
            AS ALIAS1
    FROM TABLE1 T1
   WHERE T1.COL1 = VALEUR1 AND T1.COL2 = VALEUR2
GROUP BY T1.COL1;

Citation:

Envoyé par sweetasnz

reprends la doc, c'est bien expliqué.

ok, alors explique-moi ça :

Code:

1
2
3
4
5
6
7
with table1 as (select 1 col1, 2 col2, 3 col3 , 1 valeur1, 2 valeur2 from dual),
  table2 as (select * from table1)
SELECT T1.COL1, SUM(T1.COL3), (SUM (T1.COL3)
  + (SELECT SUM(T2.COL2) FROM TABLE2 T2 WHERE T2.COL1 = T1.COL1 and t2.col2=t1.col2)) AS ALIAS1
FROM TABLE1 T1
WHERE T1.COL1 = VALEUR1 AND T1.COL2 = VALEUR2
GROUP BY T1.COL1

Code:

1
2
3
      COL1 SUM(T1.COL3)     ALIAS1
---------- ------------ ----------
         1            3          5

PS: sans doute un bogue d'optimisation

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
/* Formatted on 20.03.2012 09:57:29 (QP5 v5.203.12040.36147) */
SET ECHO ON autot on;
 
WITH table1
     AS (SELECT 1 col1,
                2 col2,
                3 col3,
                1 valeur1,
                2 valeur2
           FROM DUAL 
union all select 1,3,3,4,5 from dual where 1=0
           ),
     table2 AS (SELECT * FROM table1)
  SELECT T1.COL1,
         SUM (T1.COL3),
         (  SUM (T1.COL3)
          + (SELECT SUM (T2.COL2)
               FROM TABLE2 T2
              WHERE T2.COL1 = T1.COL1 and t2.col2=t1.col2))
            AS ALIAS1
    FROM TABLE1 T1
   WHERE T1.COL1 = VALEUR1 AND T1.COL2 = VALEUR2
GROUP BY T1.COL1;

Code:

1
2
Error at line 16
ORA-00979: not a GROUP BY expression

Bonjour,

Comme j'ai besoin de faire une jointure sur t1.col2, j'ai du ajouter t1.col2 dans mon select et dans le group by et ça marche (pas besoin de l'ajouter dans le select ça suffit dans le group by comme vous le dites)
Code:

1 2 3 4 5 6 SELECT T1.COL1, T1.COL2, SUM(T1.COL3), (SUM(T1.COL3) + (SELECT SUM(T2.COL2) FROM TABLE2 T2 WHERE T2.COL1 = T1.COL1 AND T2.COL2 = T1.COL2)) AS ALIAS1 FROM TABLE1 T1 WHERE T1.COL1 = VALEUR1 AND T1.COL2 = VALEUR2 GROUP BY T1.COL1, T1.COL2
Merci beaucoup pour votre aide!

Je voudrais savoir si le modèle de code suivant vous semble correcte et où devrais-je mettre le compteur pour connaître le nombre de lignes traitées?

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
 
   CURSOR CUR1
   IS
    SELECT T1.COL1, T1.COL2, T1.COL3
      FROM TABLE1 T1, TABLE2 T2
     WHERE T1.COL1 = T2.COL1
       AND T2.COL2 = 'VALEUR1';
 
   CURSOR CUR2(P_COL1 TABLE1.COL1%TYPE, P_COL2 TABLE1.COL2%TYPE)
   IS
    SELECT T3.COL1, SUM(T3.COL3) AS ALIAS1
      FROM TABLE3 T3
     WHERE T3.COL1 = P_COL1
       AND T3.COL2 = P_COL2
  GROUP BY T3.COL1;
 
  COMPTEUR NUMBER := 0;
 
BEGIN
  COMPTEUR := COMPTEUR + 1;
 
  FOR REC1 IN CUR1
  LOOP
    BEGIN
      FOR REC2 IN CUR2(REC1.COL1, REC1.COL2)
      LOOP
        BEGIN
          IF REC2.ALIAS1 <> REC1.COL3 THEN
              --DBMS....
          END IF;
        EXCEPTION
          WHEN OTHERS THEN
          RAISE ;
        END;
      END LOOP;
    EXCEPTION
      WHEN OTHERS THEN
        RAISE ;
    END;
  END LOOP;

Merci,

20/03/2012, 11h53
Waldar

Imbriquer des curseurs quand on peut faire une seule requête, il n'y a pas grand'chose de pire niveau performance.
20/03/2012, 12h10
Jinkas

Si je n'avais pas des difficultés, je n'aurai pas demandé de l'aide!

Merci,

Citation:

PS: sans doute un bogue d'optimisation

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
WITH table1
     AS (SELECT 1 col1,
                2 col2,
                3 col3,
                1 valeur1,
                2 valeur2
           FROM DUAL 
union ALL SELECT 1,3,3,4,5 FROM dual WHERE 1=0
           ),
     table2 AS (SELECT * FROM table1)
  SELECT T1.COL1,
         SUM (T1.COL3),
         (  SUM (T1.COL3)
          + (SELECT SUM (T2.COL2)
               FROM TABLE2 T2
              WHERE T2.COL1 = T1.COL1 AND T2.COL2=T1.COL2))
            AS ALIAS1
    FROM TABLE1 T1
   WHERE T1.COL1 = VALEUR1 AND T1.COL2 = VALEUR2
GROUP BY T1.COL1,T1.COL2;

Code:

1
2
3
      COL1 SUM(T1.COL3)     ALIAS1
---------- ------------ ----------
         1            3          5

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
WITH table1
     AS (SELECT 1 col1,
                2 col2,
                3 col3,
                1 valeur1,
                2 valeur2
           FROM DUAL 
union ALL SELECT 1,3,3,4,5 FROM dual WHERE 1=0
           ),
     table2 AS (SELECT * FROM table1)
  SELECT T1.COL1,
         SUM (T1.COL3),
         (  SUM (T1.COL3)
          + (SELECT SUM (T2.COL2)
               FROM TABLE2 T2
              WHERE T2.COL1 = T1.COL1))
            AS ALIAS1
    FROM TABLE1 T1
   WHERE T1.COL1 = VALEUR1 AND T1.COL2 = VALEUR2
GROUP BY T1.COL1;

Code:

1
2
3
      COL1 SUM(T1.COL3)     ALIAS1
---------- ------------ ----------
         1            3          5

en effet c'est pas si bien expliqué que ça ... ;)
group by :
à priori les champs dans les clauses "where" jouent également un rôle ...
bref, c'est bizarre, car on a pas besoin de spécifier T1.COL3 alors que T1.COL2 est nécessaire.
je ne l'explique pas.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
WITH table1
     AS (SELECT 1 col1,
                2 col2,
                3 col3,
                1 valeur1,
                2 valeur2
           FROM DUAL 
union ALL SELECT 1,3,3,4,5 FROM dual WHERE 1=0
           ),
     table2 AS (SELECT * FROM table1)
  SELECT T1.COL1,
         SUM (T1.COL3),
         (  SUM (T1.COL3)
          + (SELECT SUM (T2.COL2)
               FROM TABLE2 T2
              WHERE T2.COL1 = T1.COL1 AND T2.COL2=T1.COL2))
            AS ALIAS1
    FROM TABLE1 T1
   WHERE T1.COL1 = VALEUR1 AND T1.COL2 = VALEUR2
GROUP BY T1.COL1,T1.COL3,T1.COL2;

Code:

1
2
3
      COL1 SUM(T1.COL3)     ALIAS1
---------- ------------ ----------
         1            3          5

20/03/2012, 13h08
Jinkas

Merci pour votre aide mais je ne comprends pas ce que je dois faire.

Intéressant!!!

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
 
mhouri.world> desc t1
 Name              Null?    Type
 ----------------------------------------- 
 ID                         NUMBER
 N1                         NUMBER
 VC2                        VARCHAR2(10)
 VC3                        VARCHAR2(100)
 
mhouri.world> select
  2       t1.id
  3      ,sum(t1.n1)
  4      ,(sum(t1.n1) +
  5             (select
  6                   sum(t2.x1) x
  7              from  t2
  8              where t2.id  = t1.id
  9              and   t2.x1  = t1.n1
 10              )
 11        ) as alias1
 12  from   t1
 13   where t1.id   = 11
 14   and   t1.n1   = 707
 15   group by
 16         t1.id
 17        ;
            and   t2.x1  = t1.n1
                           *
ERROR at line 9:
ORA-00979: not a GROUP BY expression

Ok, il veut que je groupe par t1.n1 et bien je vais le faire

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
mhouri.world> select
  2       t1.id
  3      ,sum(t1.n1)
  4      ,(sum(t1.n1) +
  5             (select
  6                   sum(t2.x1) x
  7              from  t2
  8              where t2.id  = t1.id
  9              and   t2.x1  = t1.n1
 10              )
 11        ) as alias1
 12  from   t1
 13   where t1.id   = 11
 14   and   t1.n1   = 707
 15   group by
 16         t1.id
 17        ,t1.n1
 18        ;
 
        ID SUM(T1.N1)     ALIAS1                                                
---------- ---------- ----------                                                
        11        707

Je change légèrement ma requête en ajoutant une clause supplémentaire sur une autre colonne provenant de t1

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
 
mhouri.world> select
  2       t1.id
  3      ,sum(t1.n1)
  4      ,(sum(t1.n1) +
  5             (select
  6                   sum(t2.x1) x
  7              from  t2
  8              where t2.id  = t1.id
  9              and   t2.x1  = t1.n1
 10              and   t2.vc2 = t1.vc2
 11              )
 12        ) as alias1
 13  from   t1
 14   where t1.id   = 11
 15   and   t1.n1   = 707
 16   group by
 17         t1.id
 18        ,t1.n1
 19        ;
            and   t2.vc2 = t1.vc2
                           *
ERROR at line 10:
ORA-00979: not a GROUP BY expression

Ok, il veut que je groupe maitenant par t1.vc2 et bien je vais le faire aussi

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
 
mhouri.world> select
  2       t1.id
  3      ,sum(t1.n1)
  4      ,(sum(t1.n1) +
  5             (select
  6                   sum(t2.x1) x
  7              from  t2
  8              where t2.id  = t1.id
  9              and   t2.x1  = t1.n1
 10              and   t2.vc2 = t1.vc2
 11              )
 12        ) as alias1
 13  from   t1
 14   where t1.id   = 11
 15   and   t1.n1   = 707
 16   group by
 17         t1.id
 18        ,t1.n1
 19        ,t1.vc2
 20        ;
 
        ID SUM(T1.N1)     ALIAS1                                                
---------- ---------- ----------                                                
        11        707

Et ainsi de suite. A chaque fois qu'une colonne de la table t1(autre que t1.id) est invoquée dans le select imbriqué, je dois grouper par cette colonne

Une explication?

Peut-être en observant le plan d'exécution

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
 
mhouri.world> select * from table(dbms_xplan.display_cursor);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
 
SQL_ID  5mzq0bmusjvtg, child number 0
-------------------------------------
select      t1.id     ,sum(t1.n1)     ,(sum(t1.n1) +            (select
           sum(t2.x1) x             from  t2             where t2.id  = t1.id
            and   t2.x1  = t1.n1             )       ) as alias1 from   t1
where t1.id   = 11  and   t1.n1   = 707  group by        t1.id       ,t1.n1
 
Plan hash value: 508850092
 
--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE              |       |     1 |    10 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T2    |     1 |    10 |   103   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T2_I1 |   100 |       |     3   (0)| 00:00:01 |
|   4 |  SORT GROUP BY NOSORT        |       |     1 |     9 |     3   (0)| 00:00:01 |
|*  5 |   INDEX RANGE SCAN           | T1_N1 |     1 |     9 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("T2"."ID"=:B1)
   3 - access("T2"."X1"=:B1)
   5 - access("T1"."ID"=11 AND "T1"."N1"=707)

Je vois que le CBO sélectionne deux informations de la table t1 (id et n1) et pas uniquement n1 sur les quelles il applique le predicat 5 et transmet le resulat en ''bind variable'' (:B1) à la table T2(access("T2"."X1"=:B1)

C'est donc peut-être à cause de cela qu'il a besoin d'un group by sur n1.

20/03/2012, 14h47
mnitu

Citation:

Envoyé par Mohamed.Houri

...
Une explication?
...

Une sous-requête scalaire remplace un Outer Join. Essayez de réécrire la requête avec l’outer join.

Je ne sais plus où j'en suis:

tout ce que je demande c'est pouvoir optimiser mon code et résoudre mon problème du sum (alias total) qui j'ai l'impression ne prends pas en compte
(SUM(T3.COL3) dans total

ci-dessous les curseurs:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
  CURSOR CUR1
  IS
    SELECT T1.COL1, T1.COL2, T1.COL3
      FROM TABLE1 T1, TABLE2 T2
     WHERE T1.COL1 = T2.COL1
       AND T2.COL = VALEUR;
 
  CURSOR CUR2(P_COL1 TABLE1.COL1%TYPE, P_COL2 TABLE1.COL2%TYPE)
  IS
   SELECT T3.COL1, SUM(T3.COL3), (SUM(T3.COL3) + (SELECT SUM(T4.COL3) FROM TABLE4 T4 WHERE T4.COL1 = T3.COL1 AND T4.COL2 = T3.COL2)) AS TOTAL
     FROM TABLE3 L
    WHERE T3.COL1 = VALEUR
      AND T3.COL2 = VALEUR
 GROUP BY T3.COL1, T3.COL2

merci,

20/03/2012, 15h42
laurentschneider
ça reste un bogue du parseur qui dépend du plan. En simplifiant
Code:

1 2 3 4 WITH T AS (SELECT /*+ MATERIALIZE */ 1 x FROM DUAL) SELECT (SELECT 1 FROM dual WHERE 1=x) FROM T GROUP BY ();
retourne une erreur, mais si tu enlèves le group by, alors tu enlèves l'erreur!
20/03/2012, 16h51
Jinkas

Sincèrement je ne sais pas si on se comprend?

Citation:
Envoyé par laurentschneider
ça reste un bogue du parseur qui dépend du plan. En simplifiant
Code:

1 2 3 4 WITH T AS (SELECT /*+ MATERIALIZE */ 1 x FROM DUAL) SELECT (SELECT 1 FROM dual WHERE 1=x) FROM T GROUP BY ();
retourne une erreur, mais si tu enlèves le group by, alors tu enlèves l'erreur!
C'est la (non)transformation de la requête qui provoque le bug
Code:

1 2 3 4 5 WITH T AS (SELECT /*+ no_merge */ 1 x FROM DUAL ) SELECT (SELECT 1 FROM dual WHERE 1=x) FROM T GROUP BY ()
Ou
Code:

1 2 3 4 5 WITH T AS (SELECT 1 x FROM DUAL ) SELECT --+ NO_QUERY_TRANSFORMATION (SELECT 1 FROM dual WHERE 1=x) FROM T GROUP BY ()

20/03/2012, 18h29
laurentschneider

Citation:

Envoyé par mnitu

C'est la (non)transformation de la requête qui provoque le bug

Le bug c'est quand il n'y a pas d'erreur... mais bon, c'est pas un vrai bug, plutôt une bizarerie
20/03/2012, 18h37
Jinkas

Excusez moi de vous déranger, je ne sais plus si je suis bien sur la discussion que j'ai créee?
20/03/2012, 19h02
mnitu

Citation:

Envoyé par Jinkas

Excusez moi de vous déranger, je ne sais plus si je suis bien sur la discussion que j'ai créee?

Pourriez-vous ajouter un petit jeu d'essai ainsi que le résultat que vous attendez pour qu'on arrive à mieux comprendre ce que vous voulez dire par

Citation:

tout ce que je demande c'est pouvoir optimiser mon code et résoudre mon problème du sum (alias total) qui j'ai l'impression ne prends pas en compte
(SUM(T3.COL3) dans total

Voir 40 message(s) de cette discussion en une page