Overblog
Suivre ce blog
Editer l'article Administration Créer mon blog
5 décembre 2013 4 05 /12 /décembre /2013 10:53

Pour faire suite à mon article précédent, je vais quand même lâcher une solution viable, spécifique à Oracle : 

 

SELECT ville_etp                                                                

FROM t_entrepot                                                                 

WHERE rayon_ryn IN (SELECT rayon_ryn                                            

                    FROM t_rayon)                                               

GROUP BY ville_etp                                                              

HAVING collect(DISTINCT rayon_ryn) = (SELECT collect(rayon_ryn) FROM t_rayon)   

 

VILLE_ETP

---------

MARSEILLE

TOULOUSE 

 

Collect, à partir de 10g, permet de créer un collection par agrégation. 

Dans une collection, il n'y a pas de problématique d'ordre, par contre il peut bien y avoir des doublons (d'où l'ajout du DISTINCT).


Dans cette requête, on vérifie donc que la collection des rayons proposés par un entrepot (en limitant la liste de ces rayons à ceux présent dans la table t_rayon), correspond exactement à la liste des rayons à fournir.

 

Si on veut la correspondance exacte entre les rayons de t_entrepot et ceux de t_rayon, on peut virer la sous-requête IN.

Partager cet article

Published by Pacman - dans SQL
commenter cet article

commentaires

Laurent Schneider 06/12/2013 10:14

1) le order by fonctionne
SQL> select collect(deptno order by deptno), collect(deptno order by deptno desc) from dept

COLLECT(DEPTNOORDERBYDEPTNO)(ELEMENT) COLLECT(DEPTNOORDERBYDEPTNODESC)(ELEMENT)
--------------------------------------------------- ---------------------------------------------------
SYSTP7M4klYhXBNLgQ59n3HaLvg==(10,20,30,40) SYSTP7M4klYhXBNLgQ59n3HaLvg==(40,30,20,10)


2) le = fonctionne aussi
create type tn as table of number
/
select * from dual where tn(1,2) = tn(2,1)

l'ordre dans les ensembles n'est pas déterminant. Un sac avec une pomme et une poire = un sac avec une poire et une pomme, non?

Pacman 06/12/2013 10:34



"l'ordre dans les ensembles n'est pas déterminant."


Je suis d'accord avec toi, et j'irais même plus loin : l'ordre dans les ensembles n'existe pas. L'ordre existe par
contrainte de stockage, pour le traitement, et pour l'affichage. Donc il n'y a pas de raison en soi de définir un ordre pour une collection...


Mais je sais bien que je pinaille :)


 



Laurent Schneider 05/12/2013 18:50

j'ai déjà trouvé la distinct il y a quelques temps
http://laurentschneider.com/wordpress/2008/05/select-distinct-collect.html

mais ça ne marche pas au poil

SQL> select collect(distinct deptno order by deptno) t from emp;

T
--------------------------------------------------------------------------------
SYSTP7M4n+pSeBqbgQ59n3HbHZg==(10, 10, 10, 20, 20, 20, 20, 20, 30, 30, 30, 30, 30, 30)


Oops, mon distinct :)

Pacman 06/12/2013 09:47



Haha, t'as toujours de bonnes blagues en réserve :)


Cela dit, ça sert à quoi un ORDER BY dans un COLLECT ?


 


SQL dubuisson@QUALIF> select


  2  case when


  3  (select collect(rayon_ryn order by rayon_ryn)


  4  from t_rayon)


  5  =


  6  (select collect(rayon_ryn order by rayon_ryn desc)


  7  from t_rayon)


  8  then 'egal !' else 'différent !' end "egal ?"


  9  from dual


 10  /


 


egal ?


-----------


egal !


 


(C'est quand même plutôt rassurant que l'ordre de collecte ne change pas le résultat :))



Laurent Schneider 05/12/2013 18:26

oui, c'est possible qu'elle marchait déjà parfois dans certaines 10gR2, mais dès 11g la clause est documentée

Laurent Schneider 05/12/2013 15:17

petite précision: la clause DISTINCT est apparue en 11g

Pacman 05/12/2013 15:44



Hmmm, j'ai fait mon test en 10gR2...


 


BANNER


----------------------------------------------------------------


Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi


PL/SQL Release 10.2.0.4.0 - Production


CORE    10.2.0.4.0      Production


TNS for Linux: Version 10.2.0.4.0 - Production


NLSRTL Version 10.2.0.4.0 - Production