Probleme de requete.

Probleme de requete. - SQL/NoSQL - Programmation

Marsh Posté le 05-12-2005 à 17:24:05    

Hello,
 
   J'ai un petit probleme avec une requete sql compatible oracle ... et meme tout sgbd respectant la norme.
 
J'ai 2 tables :
 
Ligne(numero_facture,quantité,ref_article, ligne_numero) PRIMARY KEY (numero_facture, ligne_numero)
 
Article(reference, prix) PRIMARY KEY (reference)
 
Mon but est d'obtenir la facture qui a la plus grande valeur.
 
Voici la requete qui marche au top sous Postgres
 

Code :
  1. select l.numero_facture as num from ligne l
  2. join article a on l.ref_article=a.reference
  3. GROUP BY l.numero_facture
  4. ORDER BY sum(a.pu * l.quantite)
  5. DESC LIMIT 1;


 
Mais malheuresement le LIMIT n'existe pas sous Oracle. Et donc tant qu'a faire j'ai cherché une variante un peu universelle sans LIMIT ou ROWCOUNT mais j'ai du mal.  
 
Quelqu'un aurait il une idée svp ?
 
EDIT :
 
Genre comme ca :

Code :
  1. select li.numero_facture from ligne li where li.numero_facture = max(
  2. select l.numero_facture as num from ligne l
  3. join article a on l.ref_article=a.reference
  4. GROUP BY l.numero_facture
  5. ORDER BY sum(a.pu * l.quantite)
  6. DESC));


 
mais bon ca marche pas quoi

Message cité 1 fois
Message édité par Chronoklazm le 05-12-2005 à 17:37:05

---------------
Scheme is a programmable programming language ! I heard it through the grapevine !
Reply

Marsh Posté le 05-12-2005 à 17:24:05   

Reply

Marsh Posté le 05-12-2005 à 18:05:46    

T'as essayé un truc du style :
 
select numero
from
(select numero, montant
from ...)
where montant = max(montant)
 
:??:


---------------
Can't buy what I want because it's free -
Reply

Marsh Posté le 05-12-2005 à 18:18:15    

Code :
  1. select num from
  2. (select l.numero_facture as num, sum(a.pu * l.quantite) as montant from
  3. ligne_bod_rob_rep l
  4. left outer join article_bod_rob_rep a on l.ref_article=a.reference
  5. GROUP BY l.numero_facture
  6. ORDER BY sum(a.pu * l.quantite)
  7. DESC) as x
  8. where x.montant = max(x.montant);


 
Aggregates not alowed in where clause ...


---------------
Scheme is a programmable programming language ! I heard it through the grapevine !
Reply

Marsh Posté le 05-12-2005 à 18:45:59    

ah, oui...[:petrus75]
alors avec un truc genre :
 
...
where not exists (select truc from chose2 where chose2.montant>chose1.montant)


---------------
Can't buy what I want because it's free -
Reply

Marsh Posté le 05-12-2005 à 20:27:48    

skeye > tu vas finir par y arriver :D
 

Code :
  1. select l.numero_facture as num from ligne l
  2. join article a on l.ref_article=a.reference
  3. where (a.pu * l.quantite) = (select max(a2.pu * l2.quantite
  4. from ligne l2
  5. join article a2 on l2.ref_article=a2.reference
  6. )
  7. GROUP BY l.numero_facture
  8. ORDER BY sum(a.pu * l.quantite)

Message cité 1 fois
Reply

Marsh Posté le 05-12-2005 à 20:28:18    

nan, spa ça non plus :sol:

Reply

Marsh Posté le 05-12-2005 à 20:32:25    

Code :
  1. create view vfacture_amount as
  2. select l.numero_facture, sum(l.quantite * a.pu) amount
  3. from ligne l inner join article a
  4. on l.ref_article = a.reference;
  6. select v1.numero_facture
  7. from vfacture_amount v1
  8. where v1.amount = (select max(v2.amount) from vfacture_amount v2);


 
PS: la vue est inutile, on peut aussi écrire comme suit (mais c'est le bordel à relire)
 

Code :
  1. select v1.numero_facture
  2. from (
  3. select l.numero_facture, sum(l.quantite * a.pu) amount
  4. from ligne l inner join article a
  5. on l.ref_article = a.reference
  6. ) v1
  7. where v1.amount = (select max(v2.amount) from (
  8. select l.numero_facture, sum(l.quantite * a.pu) amount
  9. from ligne l inner join article a
  10. on l.ref_article = a.reference
  11. ) v2);

Reply

Marsh Posté le 05-12-2005 à 21:47:14    

Arjuna a écrit :

skeye > tu vas finir par y arriver :D


 
Flemme de tester...[:petrus75]


Message édité par skeye le 05-12-2005 à 21:47:28

---------------
Can't buy what I want because it's free -
Reply

Marsh Posté le 06-12-2005 à 17:23:28    

Code :
  1. SELECT num FROM (
  2. SELECT l.numero_facture as num
  3. FROM ligne l, article a
  4. WHERE l.ref_article=a.reference
  5. GROUP BY l.numero_facture
  6. ORDER BY sum(a.pu * l.quantite) DESC)
  7. WHERE rownum = 1;

Message cité 1 fois
Reply

Marsh Posté le 06-12-2005 à 17:25:49    

Beegee a écrit :

Code :
  1. SELECT num FROM (
  2. SELECT l.numero_facture as num
  3. FROM ligne l, article a
  4. WHERE l.ref_article=a.reference
  5. GROUP BY l.numero_facture
  6. ORDER BY sum(a.pu * l.quantite) DESC)
  7. WHERE rownum = 1;



 

Chronoklazm a écrit :


'ai cherché une variante un peu universelle sans LIMIT ou ROWCOUNT mais j'ai du mal.  


 
Je pense que rownum n'es tpas accepté non plus...[:god]


Message édité par skeye le 06-12-2005 à 17:26:23

---------------
Can't buy what I want because it's free -
Reply

Marsh Posté le 06-12-2005 à 17:25:49   

Reply

Marsh Posté le 06-12-2005 à 17:35:48    

rownum ne marche que sur Oracle pour être plus exact, donc c'est tout aussi limitatif car non portable.
 
cherchez pas, la norme sql92 n'avait pas prévu du tout de filtrage sur les "n premiers tuples".
 
postgre est le premier à avoir ajouté cette fonction, avec "TOP" (et pour cette raison elle a été ajouté dans SQL 99, PostGre étant le SGBD le plus proche de la norme, ses propositions pour la norme sont prioritaires sur les autres produits)
ça a été repris par DB2 il me semble, access et SQL Server
 
mysql a ajouté son LIMIT
 
oracle a utilisé rownum qui ne permet pas de faire ce qu'on veut (bien plus limité que TOP et LIMIT (limit étant le plus souple))


Message édité par Arjuna le 06-12-2005 à 17:37:55
Reply

Marsh Posté le 06-12-2005 à 20:24:33    

Il n'est pas plus limité, il est juste plus chiant à écrire.

Reply

Marsh Posté le 06-12-2005 à 20:50:40    

si, il est limité car il est quasi impossible de faire une requête équivalente ) "limit 5, 10".
 
avec des top imbriqués, on peut mais c'est chiant. avec le rownum, c'est la merde car il ne s'applique pas aux sous-requêtes

Reply

Marsh Posté le 06-12-2005 à 20:53:51    

Code :
  1. SELECT * FROM
  2. (SELECT * FROM
  3. (SELECT * FROM maTable
  4. ORDER BY monChamp)
  5. WHERE rownum <= 10)
  6. WHERE rownum >= 5;


 
C'est une habitude à prendre ... :D
 
Et puis en l'écrivant de cette façon, Oracle optimise le plan d'exécution.

Reply

Marsh Posté le 06-12-2005 à 22:45:27    

hmmm... ça marche à partir de quelle version ?
 
parceque pour moi, rownum compte à partir de la première ligne retournée. ainsi :
 
where rownum <= x
=> Retourne X lignes, de 1 à X
 
where rownum >= x
=> Ne retroune aucune ligne, la première étant = à 1, et supprimée, alors la seconde est égale à 1 aussi et est supprimée, et ainsi de suite
 
where rownum = x
=> Retourne la première ligne si x = 1, 0 sinon. (pour la même raison que précédement)
 
C'est en tout cas le comportement que j'ai pu constater avec Oracle 7.3 et Oracle 8.0.5, confirmé par la documentation de ces deux versions.
 
Ceci dit, je n'ai jamais testé avec une version plus récente. De plus, avec ces version :
 
select * from
(select champs, ..., rownum from matable)
 
=> ca plante, "rownum" étant interdit dans une sous-requête. peut-être que ça a changé depuis, vais essayer ça dès demain au boulot :)

Reply

Marsh Posté le 06-12-2005 à 22:52:19    

t'ain, ça marche avec la 9i chais plus combien ! :love:
 
http://magicbuzz.multimania.com/files/rownum.PNG
 
PS: Je vous rassure, chuis pas retourné au boulot en 5 minutes, y'a juste que je viens de tilter que je pouvais me connecter au taff avec Citrix Metaframe :)
 
Bien pratique de lancer TOAD depuis mon PC alors qu'il s'exécute réellement sur un serveur du taff !

Reply

Marsh Posté le 06-12-2005 à 22:52:44    

Ca m'apprendra à taper trop vite, en fait il faut aliaser le rownum dans la sous-requête pour que ça marche :)
 
SELECT * FROM  
(SELECT rownum as rnum, sub.* FROM  
(SELECT * FROM maTable  
ORDER BY monChamp) sub
WHERE rownum <= 10)
WHERE rnum >= 5;

Reply

Marsh Posté le 06-12-2005 à 22:53:21    

En regardant les codes produit de la base de prod au boulot, je me demande pourquoi j'ai pas fait les tests sur la base de dev, elle à l'air plus propre :lol:

Reply

Marsh Posté le 06-12-2005 à 22:54:01    

Ceci dit, sûr et certain, jusqu'à la version 8.0.5, ça ne marchait pas !
 
Tiens, vais faire un test d'ailleurs, j'ai un doute affreux...

Reply

Marsh Posté le 06-12-2005 à 23:01:22    

[MagicBuzz qui découvre oracle, sur lequel il travaille depuis 6 ans]
 
select rownum, codpro
from (select codpro from pro
where codsoc = 2 order by codpro)
where rownum <= 5
 
t'ain, c'est tout le fonctionnement de rownum qui a changé en fait !
avant, un test sur le rownum s'executait comme un having, c'est à dire une fois la requête terminée et les tri effectués
 
et maintenant, on peut utiliser order by dans une sous-requête !
 
[/MagicBuzz qui découvre oracle, sur lequel il travaille depuis 6 ans]

Reply

Marsh Posté le 06-12-2005 à 23:02:42    

en fait, maintenant, c'est limite plus souple que TOP n, mise à part qu'on doit forcément passer par une sous-requête si on veut que ça respecte le tri

Reply

Marsh Posté le 07-12-2005 à 07:27:52    

Ah mais alors ça c'est bon à savoir! [:huit]


Message édité par skeye le 07-12-2005 à 07:28:00

---------------
Can't buy what I want because it's free -
Reply

Marsh Posté le 07-12-2005 à 09:51:00    

Code :
  1. SELECT * FROM 
  2. (SELECT ROWNUM AS rnum, sub.* FROM 
  3. (SELECT * FROM CUSTOMER 
  4. ORDER BY customer_ref) sub
  5. WHERE ROWNUM <= 10)
  6. WHERE rnum >= 5;


 
Plan d'exécution avec ce type de requête Oracle :
 
SELECT STATEMENT Hint=RULE  
  VIEW
    COUNT STOPKEY
      VIEW
        TABLE ACCESS BY INDEX ROWID      CUSTOMER
          INDEX FULL SCAN                       CUSTOMER_PK
 
On voit bien le COUNT STOPKEY qui signifie que l'index (ici le champ est indexé) est parcouru, mais pas intégralement (on s'arrête à la 10ème valeur).
 
Evidemment, il ne faut pas inverser la requête et faire rownum >= 5 en 1er, car alors rien n'est renvoyé (étant donné que le rownum ne s'incrémente que lorsqu'une ligne est renvoyée ...).

Reply

Marsh Posté le    

Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed