Probleme de requete.

Recherche :

Mot : Pseudo : Filtrer
Bas de page
Auteur	Sujet : Probleme de requete.

Chronoklazm

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 :

select l.numero_facture as num from ligne l
join article a on l.ref_article=a.reference
GROUP BY l.numero_facture
ORDER BY sum(a.pu * l.quantite)
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 :

select li.numero_facture from ligne li where li.numero_facture = max(
select l.numero_facture as num from ligne l
join article a on l.ref_article=a.reference
GROUP BY l.numero_facture
ORDER BY sum(a.pu * l.quantite)
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 !

Publicité

skeye

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 -

Chronoklazm

Code :

select num from
(select l.numero_facture as num, sum(a.pu * l.quantite) as montant from
ligne_bod_rob_rep l
left outer join article_bod_rob_rep a on l.ref_article=a.reference
GROUP BY l.numero_facture
ORDER BY sum(a.pu * l.quantite)
DESC) as x
where x.montant = max(x.montant);

Aggregates not alowed in where clause ...

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

skeye

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 -

Arjuna

Aircraft Ident.: F-MBSD

skeye > tu vas finir par y arriver

Code :

select l.numero_facture as num from ligne l
join article a on l.ref_article=a.reference
where (a.pu * l.quantite) = (select max(a2.pu * l2.quantite
from ligne l2
join article a2 on l2.ref_article=a2.reference
)
GROUP BY l.numero_facture
ORDER BY sum(a.pu * l.quantite)

Message cité 1 fois

Arjuna

Aircraft Ident.: F-MBSD

nan, spa ça non plus :sol:

Arjuna

Aircraft Ident.: F-MBSD

Code :

create view vfacture_amount as
select l.numero_facture, sum(l.quantite * a.pu) amount
from ligne l inner join article a
on l.ref_article = a.reference;
select v1.numero_facture
from vfacture_amount v1
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 :

select v1.numero_facture
from (
select l.numero_facture, sum(l.quantite * a.pu) amount
from ligne l inner join article a
on l.ref_article = a.reference
) v1
where v1.amount = (select max(v2.amount) from (
select l.numero_facture, sum(l.quantite * a.pu) amount
from ligne l inner join article a
on l.ref_article = a.reference
) v2);

skeye

Arjuna a écrit :

skeye > tu vas finir par y arriver

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 -

Beegee

Code :

SELECT num FROM (
SELECT l.numero_facture as num
FROM ligne l, article a
WHERE l.ref_article=a.reference
GROUP BY l.numero_facture
ORDER BY sum(a.pu * l.quantite) DESC)
WHERE rownum = 1;

Message cité 1 fois

skeye

Beegee a écrit :

Code :

SELECT num FROM (
SELECT l.numero_facture as num
FROM ligne l, article a
WHERE l.ref_article=a.reference
GROUP BY l.numero_facture
ORDER BY sum(a.pu * l.quantite) DESC)
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 -

Publicité

Arjuna

Aircraft Ident.: F-MBSD

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

Beegee

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

Arjuna

Aircraft Ident.: F-MBSD

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

Beegee

Code :

SELECT * FROM
(SELECT * FROM
(SELECT * FROM maTable
ORDER BY monChamp)
WHERE rownum <= 10)
WHERE rownum >= 5;

C'est une habitude à prendre ...

Et puis en l'écrivant de cette façon, Oracle optimise le plan d'exécution.

Arjuna

Aircraft Ident.: F-MBSD

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

Arjuna

Aircraft Ident.: F-MBSD

t'ain, ça marche avec la 9i chais plus combien ! :love:

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 !

Beegee

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;

Arjuna

Aircraft Ident.: F-MBSD

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:

Arjuna

Aircraft Ident.: F-MBSD

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...

Arjuna

Aircraft Ident.: F-MBSD

[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]

Arjuna

Aircraft Ident.: F-MBSD

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

skeye

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 -

Beegee

Code :

SELECT * FROM
(SELECT ROWNUM AS rnum, sub.* FROM
(SELECT * FROM CUSTOMER
ORDER BY customer_ref) sub
WHERE ROWNUM <= 10)
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 ...).

Publicité

FORUM HardWare.fr

Programmation

SQL/NoSQL

Probleme de requete.

Sujets relatifs
[résolu] Requete MySQL : problème d'affichage par catégorie	[ACCESS] probleme cumul sur requete
Problème Requete / Timestamp	Probléme avec une requete
[SQL*PLUS] Probleme avec caractere special & dans requete SQL	probleme de requete
Probleme de requete et de texte en php [Résolu, euh en fait non]	Probleme de requete
problème de requête	[VBA] Access, probleme de requete
Plus de sujets relatifs à : Probleme de requete.

Page générée en 0.083 secondes