ERROR: could not identify an ordering operator for type record [Résolu]

[Benoit_Durand]

Bonjour à tous,

J'ai du récemment porter une appli d'Oracle 10g vers PostgreSQL 8.2.4 sur du windows XP.

Voici l'erruer que j'obtiens en exécutant certaines requêtes :
ERROR: could not identify an ordering operator for type record
État SQL :42883
Astuce : Use an explicit ordering operator or modify the query.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT * FROM
  (SELECT sum(CA) AS CA ,sum(Marge) AS Marge, avg(N) AS N, sum(CA2) AS CA2 ,sum(Marge2) AS Marge2 , avg(N2) AS N2, c.CLI_NOMCLIENT 
  FROM
    (SELECT sum(BUD_CARVALEUR) AS CA ,sum(BUD_MAVALEUR) AS Marge ,PER_NUMANNEE AS N,CLI_IDCLIENT 
      FROM IND_BUDGET,CLIENT,PERIODE,SITE,AGENCE 
      WHERE BUD_IDPERIODE=PER_IDPERIODE AND BUD_IDCLIENT=CLI_IDCLIENT AND PER_NUMANNEE=2007 
        AND BUD_IDSITE=SIT_IDSITE AND SIT_IDAGE=AGE_IDAGE AND AGE_LIBAGENCE LIKE 'Aqui'
      GROUP BY (PER_NUMANNEE,CLI_IDCLIENT) ORDER BY CA DESC) r LEFT OUTER JOIN
    (SELECT sum(BUD_CARVALEUR) AS CA2 ,sum(BUD_MAVALEUR) AS Marge2 ,PER_NUMANNEE AS N2 ,CLI_IDCLIENT 
      FROM IND_BUDGET,CLIENT,PERIODE,SITE,AGENCE 
      WHERE BUD_IDPERIODE=PER_IDPERIODE AND BUD_IDCLIENT=CLI_IDCLIENT AND PER_NUMANNEE=2007-1 
        AND BUD_IDSITE=SIT_IDSITE AND SIT_IDAGE=AGE_IDAGE AND AGE_LIBAGENCE LIKE 'Aqui'
      GROUP BY (PER_NUMANNEE,CLI_IDCLIENT)) r2 ON r.CLI_IDCLIENT=r2.CLI_IDCLIENT
    INNER JOIN CLIENT c ON r.CLI_IDCLIENT=c.CLI_IDCLIENT
      GROUP BY (CLI_NOMCLIENT) ORDER BY CA DESC
  )q
WHERE rownum <= 10

Savez vous comment se corrige le problème ?

Cordialement

[hpalpha]

Bonjour,

en fait, tu as mis tes order by entre parentheses, pg pense donc qu'il doit gere un record, mais il ne peux pas (ou il faut que creer les operators toi meme)
enleve les parentheses

sinon pour le where rownum <= 10, remplace pas limit 10

SELECT * FROM
(SELECT sum(CA) AS CA ,sum(Marge) AS Marge, avg(N) AS N, sum(CA2) AS CA2 ,sum(Marge2) AS Marge2 , avg(N2) AS N2, c.CLI_NOMCLIENT
FROM
(SELECT sum(BUD_CARVALEUR) AS CA ,sum(BUD_MAVALEUR) AS Marge ,PER_NUMANNEE AS N,CLI_IDCLIENT
FROM IND_BUDGET,CLIENT,PERIODE,SITE,AGENCE
WHERE BUD_IDPERIODE=PER_IDPERIODE AND BUD_IDCLIENT=CLI_IDCLIENT AND PER_NUMANNEE=2007
AND BUD_IDSITE=SIT_IDSITE AND SIT_IDAGE=AGE_IDAGE AND AGE_LIBAGENCE LIKE 'Aqui'
GROUP BY PER_NUMANNEE,CLI_IDCLIENT ORDER BY CA DESC) r LEFT OUTER JOIN
(SELECT sum(BUD_CARVALEUR) AS CA2 ,sum(BUD_MAVALEUR) AS Marge2 ,PER_NUMANNEE AS N2 ,CLI_IDCLIENT
FROM IND_BUDGET,CLIENT,PERIODE,SITE,AGENCE
WHERE BUD_IDPERIODE=PER_IDPERIODE AND BUD_IDCLIENT=CLI_IDCLIENT AND PER_NUMANNEE=2007-1
AND BUD_IDSITE=SIT_IDSITE AND SIT_IDAGE=AGE_IDAGE AND AGE_LIBAGENCE LIKE 'Aqui'
GROUP BY PER_NUMANNEE,CLI_IDCLIENT) r2 ON r.CLI_IDCLIENT=r2.CLI_IDCLIENT
INNER JOIN CLIENT c ON r.CLI_IDCLIENT=c.CLI_IDCLIENT
GROUP BY CLI_NOMCLIENT ORDER BY CA DESC
)q
LIMIT 10

[Benoit_Durand]

Tout simplement....

Big Merci