[11g][10g] Group by via db link de 11g à 10g

Version imprimable

Bonjour,

Je bloque sur une requête pas très compliquée, mais je n'arrive pas à comprendre le problème exact.

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
select	Main_Service_Number, 
		Time_Slot, 
		SUM(Direct_Entered_Call) as Tot_Direct_Entered_Call, 
		Call_Group
from
    (	select	a.DNIS || ' (' || NVL(b.DINA, a.DNIS) || ')' as Main_Service_Number,
				a.CATI as Time_Slot,
				a.TCDQ as Direct_Entered_Call,
				case
					when a.ROPO IN (47000,47001,47009,47010,47011,47002,47014)
						then '1st'
					when a.ROPO IN (47015)
						then 'AL'
					when a.ROPO IN (47022,47025,47012)
						then 'SUB'
					when a.ROPO IN (40381,40384,40391,45115,45729,40382,40385,40392,45569,40383,40386,40393,45451,45612,45636,40388,40394,45111,45220,45219,40395)
						then 'CM'
					when a.ROPO IN (47016,47017,47018,45416,45415,44696,45216,45225,45266,45977)
						then 'PA'
					when a.ROPO IN (47003,47013,45457,45571,45458,45459,45460)
						then 'DM'
					when a.ROPO IN (47020,47021,45973,45974,45975,47024)
						then 'PET'
					else NULL
				end as Call_Group
		from CTMP.HDIGRE@CTMP_R a
		INNER JOIN CTMP.DNISMA@CTMP_R b ON a.DNIS = b.DNIS
		where a.CADA = date '2015-10-25'
		and a.CATI Between '09' And '20'
		and a.ROPO IN (47000,47001,47009,47010,47011,47002,47014,47015,47022,47025,47012,40381,40384,40391,45115,45729,40382,40385,40392,45569,40383,40386,40393,45451,45612,45636,40388,40394,45111,45220,45219,40395,47016,47017,47018,45416,45415,44696,45216,45225,45266,45977,47003,47013,45457,45571,45458,45459,45460,47020,47021,45973,45974,45975,47024)	
	)
group by Main_Service_Number, Time_Slot, Call_Group

Comme vous pouvez le voir, j'utilise un dblink. La base sur laquelle j'execute la requête est 11g et la base du db link est 10g
Le problème est le suivant :

Citation:

ORA-00979: GROUP BYの式ではありません。(N'est pas une expression GROUP BY)
ORA-02063: 先行のエラー・メッセージを参照してくださいline(CTMP_R)。(preceding line from CTMP_R)

Si je mets le bloc case dans le group by directement, ca fonctionne.
Donc je peux toujours passer par cette solution. Mais si vous pouviez m'expliquer pourquoi dans ce cas ci-dessus ca bloque, ca pourrait être interessant.

Merci d'avance.

Steven

13/11/2015, 08h33
escartefigue

Faites + simple avec la syntaxe "group by 1, 2, 3..."

Tu peux préciser Escartefigue ce que tu entends par là ?

Sinon, j'ai essayé une requête similaire entre une base 11g et 10g, je n'ai pas eu de problèmes. Donc je ne vois pas d'erreur flagrante.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 SELECT grp, tcli, SUM(nb) FROM ( SELECT numdevis, client ||' ('|| typ ||')' tcli, CASE WHEN numdevis IN ('D24', 'D26') THEN '1st' WHEN numdevis = 'D05' THEN '2nd' WHEN numdevis = 'D19' THEN '3rd' ELSE NULL END grp FROM LSTDEVIS@Base10 a JOIN LSTYP@Base10 b ON b.typ = a.typ WHERE numdevis IN ('D24', 'D05', 'D26', 'D19', 'D17') ) GROUP BY grp, tcli; GRP TCLI SUM(NB) "EDT (D)" 30 "2nd" "EDT (D)" 36 "3rd" "EDT (D)" 35 "1st" "EDT (D)" 71
Est-ce un problème de character set entre les bases ?

13/11/2015, 16h11
mnitu

Il se peut que c'est un bug suite à la transformation de la requête. Essayez d'ajouter le hint NO_QUERY_TRANSFORMATION pour vérifier.
16/11/2015, 01h34
bstevy

Merci pour vos réponses.

@escartefigue: j'ai jamais vu la syntaxe "group by 1,2,3" fonctionner sous oracle (je l'utilisais sous teradata par contre). Je sais que ca fonctionne avec le order, mais avec le group by, ca m'a toujours renvoyé une erreur, et dans ce cas aussi.

@McM: ce n'est pas véritablement un problème de group by en soit. Je peux faire tourner cette requete en supprimant la colonne Call_Group et ca fonctionne alors parfaitement. Donc j'ai un peu du mal à savoir d'ou ca peut venir. Je vois dans la tienne un case aussi, donc je me dis que ca doit pas etre ca le probleme. Peut etre le nombre d'element sur lequel on fait le group by ?

@mnitu: Peux tu es plus précis stp ? comment j'utilise cela?

Citation:

Envoyé par bstevy

Merci pour vos réponses.

...
@mnitu: Peux tu es plus précis stp ? comment j'utilise cela?

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
 
select  /*+ NO_QUERY_TRANSFORMATION */ Main_Service_Number, 
    Time_Slot, 
    SUM(Direct_Entered_Call) as Tot_Direct_Entered_Call, 
    Call_Group
from
    ( select  a.DNIS || ' (' || NVL(b.DINA, a.DNIS) || ')' as Main_Service_Number,
        a.CATI as Time_Slot,
        a.TCDQ as Direct_Entered_Call,
        case
          when a.ROPO IN (47000,47001,47009,47010,47011,47002,47014)
            then '1st'
          when a.ROPO IN (47015)
            then 'AL'
          when a.ROPO IN (47022,47025,47012)
            then 'SUB'
          when a.ROPO IN (40381,40384,40391,45115,45729,40382,40385,40392,45569,40383,40386,40393,45451,45612,45636,40388,40394,45111,45220,45219,40395)
            then 'CM'
          when a.ROPO IN (47016,47017,47018,45416,45415,44696,45216,45225,45266,45977)
            then 'PA'
          when a.ROPO IN (47003,47013,45457,45571,45458,45459,45460)
            then 'DM'
          when a.ROPO IN (47020,47021,45973,45974,45975,47024)
            then 'PET'
          else NULL
        end as Call_Group
    from CTMP.HDIGRE@CTMP_R a
    INNER JOIN CTMP.DNISMA@CTMP_R b ON a.DNIS = b.DNIS
    where a.CADA = date '2015-10-25'
    and a.CATI Between '09' And '20'
    and a.ROPO IN (47000,47001,47009,47010,47011,47002,47014,47015,47022,47025,47012,40381,40384,40391,45115,45729,40382,40385,40392,45569,40383,40386,40393,45451,45612,45636,40388,40394,45111,45220,45219,40395,47016,47017,47018,45416,45415,44696,45216,45225,45266,45977,47003,47013,45457,45571,45458,45459,45460,47020,47021,45973,45974,45975,47024) 
  )
group by Main_Service_Number, Time_Slot, Call_Group

17/11/2015, 03h49
bstevy

Wahou!!!
ca fonctionne 8O

Mais genre, c'est nickel quoi. Un grand merci.

Je peux avoir un peu plus d'explication sur ce commentaire magique ?
qu'est ce qui se cache dessous ?

Est ce qu'il vaut mieux l'utiliser systematiquement quand je passe par mon dblink, ou c'est au cas par cas ?
18/11/2015, 14h32
mnitu

Non, l'utilisation des Hint SQL doit rester une manière exceptionnelle de résolution des problèmes. Elle doit être correctement documenté et devrait faire l'objet d'une révision au passage d'une autre version d'Oracle.

Commencez plutôt par vérifier si un patch n'est déjà pas disponible pour votre version d'Oracle et votre plate-forme qui corrige ce type d'anomalie et demander à votre DBA de l'installer. Comme tout logiciel écrit par des humains Oracle est boguée.

L'explication est simple: Oracle n'exécute pas toujours la requête tel que vous l'avez écrite. Pour l'optimiser il prends la liberté de la transformer c'est-à-dire de la réécrire internement ce qui peut lui ouvrir des chemins d'optimisation nouvelles. Bien sur que la nouvelle requête doit rester équivalente à celle que vous avez écrit. Plus les versions d'Oracle avancent, plus ce composant de l'optimiseur devient plus sophistiqué et sophistiqué veut dire aussi plus difficile à tester et par conséquence plus susceptible à bugger. Normalement ces problèmes sont corrigés dans les patchs.
19/11/2015, 04h17
bstevy

Ok, merci pour ces infos.

Sinon, une autre solution que j'ai utilisée aussi pour résoudre ce problème, c'est transformer le case en une jointure gauche avec une cte.
Et dans ce cas, oracle me l'execute bien.
19/11/2015, 10h11
mnitu

Oui c'est tout à fait possible. La façon dans laquelle la requête est écrite peut interdire certaines transformations pour l'optimiseur.