Doublure de champs dans un select :P [Résolu]

[cbprogs]

Bonjour,

J'ai deux tables comme ci-dessous :
- actif (act_Num)
- carac (car_Num, lbl)

Une relation :
- possède (numAct, numCar, valeur)

Mon SHEMA : ACTIF - (0,n) - POSSEDE - (0,n) - CARAC

Pour essayer de faire simple, je souhaite obtenir les caractéristiques d'un actif.
Cependant je dois les afficher par colonnes.

Exemple :
- j'ai un actif carte SIM.
- dans caractéristique j'ai PIN et PUK

Je voudrais une requete de ce genre :

Code :

1
2
3
4
SELECT valeur AS 'PIN', valeur AS 'PUK'
FROM pos
JOIN carac ON car_Num=numCar
WHERE numAct=123

Je sais que c'est impossible mais y a-t-il un moyen d'afficher ce "doublon" de champs qui est en fait deux colonnes ?

J'espère que c'est assez clair héhé

Merci

[Drizzt [Drone38]]

Pour un actif donné tu veux avoir toutes les caractéristiques en colonnes c'est ça ? Et il peut y avoir entre 0 et N caractéristiques.


Si tu as une borne à N tu peux t'en sortir avec la fonction LAG.

cf : Sujet similaire

[cbprogs]

En fait je ne veux que certaines caractéristiques (dont je ne connais que le label) et non toutes. Donc je suis dans l'obligation de faire une jointure avec la table des caractéristiques.
Je dois afficher ces dernières en colonnes oui.

J'ai regardé le sujet cité mais j'avoue ne pas tout comprendre.

Ma requête doit donc être dans ce genre ?

Code :

1
2
3
4
5
SELECT lag(valeur) over (pos BY carac.lbl='PIN') 'PIN',  
           lag(valeur) over (pos BY carac.lbl='PUK') 'PUK'
FROM pos
JOIN carac ON car_Num=numCar
WHERE numAct=123

[Drizzt [Drone38]]

Pas exactement,

Tiens je pense que ceci devrais fonctionner :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT PIN, PUK FROM
(
    SELECT valeur PIN,
           lead(valeur,1) over (partition BY actNum ORDER BY lbl) PUK, 
           r
    FROM (
          SELECT a.*, c.*, 
                   row_number() over(partition BY actNum ORDER BY lbl) r 
            FROM actif a, carac c
         )
      LEFT OUTER JOIN pos
        ON car_Num=numCar AND numAct = actNum
    WHERE lbl IN ('PIN', 'PUK')
) WHERE r=1

En gros :
- Produit cartésien entre tes actifs et les caractéristiques, en limitant aux caractéristiques dans une liste par libellelé (tu peux aussi filtrer en te limitant à certains actif). Cela permettera de remonter tout de même des colonnes vides si les caractéristiques n'existent pas pour les actifs.
- On numérote ces lignes par actif dans l'ordre des caractéristiques
- Jointure ouverte sur la relation possède.
- De ceci on recupère la valeur de la caractéristique courante et de la suivante.
- Enfin on ne garde que la première ligne de chaque actif, les autres étant incomplètes.

J'espère n'avoir rien oublié.
Ca fonctionne sur des petits exemples avec 2 actifs dont un n'ayant pas de PIN.

Je laisse soin aux experts de ce forum d'améliorer/corriger/commenter.

[cbprogs]

J'avoue ne pas comprendre tout a fait.

Lorsque je garde la sous requete, ca me retourne que des R = 10 et 11
Or dans ma base j'ai bien plus d'actifs avec PIN et PUK renseignés.

Ce ne doit pas être tout a fais juste

[Drizzt [Drone38]]

Effectivement, il faut déplacer le filtre sur les caractéristiques, sinon tu n'es pas sur d'avoir PIN qui correspond à R=1

Je n'avais mis que PIN et PUK dans mes tests et avais donc zappé ce point.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT PIN, PUK FROM
(
    SELECT valeur PIN,
           lead(valeur,1) over (partition BY actNum ORDER BY lbl) PUK, 
           r
    FROM (
          SELECT a.*, c.*, 
                   row_number() over(partition BY actNum ORDER BY lbl) r 
            FROM actif a, carac c
          WHERE lbl IN ('PIN', 'PUK')
         )
      LEFT OUTER JOIN pos
        ON car_Num=numCar AND numAct = actNum
) WHERE r=1

[cbprogs]

En fait ca ne change pas grand chose. J'ai du doublon.

MCD : http://cbprogs.free.fr/sql1.JPG
JEU : http://cbprogs.free.fr/sql2.JPG

[Drizzt [Drone38]]

Dans ton sql2.jpg peut tu ajouter r et rim_act dans le résultat final ?

Et le résultat de la sous requete qui commence par SELECT a.*, c.* ...

A priori de ce que je vois l'ajout de la condition R=1 devrait te ramener les lignes souhaitées. Il y a juste les deux lignes à null, null qui m'embêtent un peu.



(Note tu as mis deux fois la condition sur car_lbl, la seconde est inutile).

[cbprogs]

C'est bon sauf pour rim_act.

[Drizzt [Drone38]]

Bon il aurait fallut act_num, mais tu pourras certainement répondre toi même.

Dans ta table rim_act, tu n'aurais pas un element sur deux avec un PIN/PUK et l'autre sans ?

D'apres l'extract, il fait ce qu'il faut, à savoir sortir deux lignes par act_num (on voit que R ne prend que les valeurs 1 et 2, et vu qu'on a partionné par act_num chaque couple 1,2 de R correspond à un act_num différent).

On a donc des couples de lignes null,null, qui doivent correspondre à des act_num sans PIN/PUK et des couples avec une première ligne contenant le PIN et le PUK et la seconde le PUK et NULL. Ce qui est normal. Le filtre where R=1 que tu as supprimé permet de ne conserver que la première ligne contenant le résultat souhaité.

A toi de voir comment gérer les act_num sans PIN/PUK. Si tu ne les veux pas et que tu es sur qu'il ne peut pas y avoir d'act_num avec un PIN sans PUK et vice-versa, tu peux simplement remplacer le LEFT OUTER JOIN par un JOIN.

Sinon tu filtres simplement en fin de requete les lignes completement NULL, ou avant si tu sais à l'avance les act_num que tu souhaites.


Bref la requête semble bien fonctionner il faut juste remettre le filtre sur R.

[cbprogs]

Dans ma table actif il n'y a que des actif.
C'est dans la relation (possède) qu'il y a les laison des PIN/PUK.

Et non ils sont tous ou quasi avec des PIN PUK. Il n'y en a pas un sur deux.

J'ai testé avec un JOIn a la place d'un LEFT OUTER JOIN et ca a l'air d'être bon.

J'avoue galérer en SQL complexe comme cela.
J'ai regardé mais je ne comprend pas trop les LEFT/RIGHT OUTER/INNER JOIN.

Pourrais tu me faire un topo stp ?

[Drizzt [Drone38]]

Avec le JOIN fait juste bien attention, si tu as des actifs avec le PIN ou le PUK manquant (en fait surtout le PIN), tu auras un résultat erroné.

Pour vérifier que la requete avec le LEFT OUTER est correcte, il te suffit de remonter l'id de l'actif et de vérifier que ceux avec des NULL, NULL n'ont pas de PIN/PUK.

Sinon pour les différence entre les jointures, je ne pense pas être le mieux placé pour te faire un cours la dessus.
Regarde les tutoriaux de ce site, je suis sur que tu trouveras une explication avec des exemples qui te satisfera.

Sinon un dessin valant mieux qu'un long discours, fais des tests sur des petites requetes.

[cbprogs]

OK merci bien Chef

[cbprogs]

Re Drizzt,

Je souhaite ajouté la valeur IMEI aux caractéristiques.
Comment dois je procéder pour afficher la nouvelle colonne ?

J'ai testé en rajoutant un lead avec la valeur 2 mais ca m'affiche n'importe quoi!
...
lead(pos_val,2) over (partition BY act_num ORDER BY car_lbl) IMEI
...
WHERE Lower(car_lbl) IN ('pin','puk','imei')
...

Après le r tout en bas avec WHERE r=1.
Si je change en 2 ou autre c'est pareil : n'importe quoi.

Merci d'avance

[Drizzt [Drone38]]

Si tu ajoutes IMEI, tes caractéristiques vont être triées de la façon suivante :

1. IMEI
2. PIN
3. PUK

Du coup dans ton select, valeur devient IMEI, lead(valeur,1) PIN et lead(valeur,2) PUK.

Le reste devrait être inchangé. Par contre attention, si tu as choisi le JOIN à la place de mon LEFT OUTER JOIN, tu auras effectivement n'importe quoi si certaines caractéristiques n'existent pas pour un actNum donné.

[cbprogs]

Merci de ta réponse.

En fait ca foire meme avec le LEFT OUTER JOIN.

Voir ici : http://cbprogs.free.fr/sql.JPG

Je ne comprend pas trop le resultat par rapport à la requete.

[Drizzt [Drone38]]

Tu as ajouté des JOIN, certains semblent te ramener plus d'une ligne est-ce normal ?

Tu as pour certains NUMIMMO plusieurs CODESITE, ceci perturbe la requête ainsi construite qui attend un nombre précis de ligne par NUMIMMO.
(ex: NUMIMMO : 2987 et 4186)

Ensuite du résultat de la requête pour moi il trouve le PIN pour chaque NUMIMMO mais pas de PUK et IMEI.
Es-tu sur que les liens rim_act - rim_pos - rim_car existent pour PUK et IMEI ?
Si oui supprime le where R=1, supprime les lead, ajoute car_lbl dans le select et vois ce que tu obtiens. As-tu bien tes valeurs pour chaque caractéristique ?

[cbprogs]

Tu as ajouté des JOIN, certains semblent te ramener plus d'une ligne est-ce normal ? J'ai rajouter des JOINS pour les jointures sur d'autres tables. Donc ca parait normal.

Tu as pour certains NUMIMMO plusieurs CODESITE, ceci perturbe la requête ainsi construite qui attend un nombre précis de ligne par NUMIMMO.
(ex: NUMIMMO : 2987 et 4186)
Justement une ligne il trouve un PUK et pas l'AUTRE et il inverse le PIN avec l'IMEI. Donc je cmprend pas !

Ensuite du résultat de la requête pour moi il trouve le PIN pour chaque NUMIMMO mais pas de PUK et IMEI.
Es-tu sur que les liens rim_act - rim_pos - rim_car existent pour PUK et IMEI ?
Si oui supprime le where R=1, supprime les lead, ajoute car_lbl dans le select et vois ce que tu obtiens. As-tu bien tes valeurs pour chaque caractéristique ? OUI pour chaque actif j'ai ces 3 caractéristiques.
J'ai mis à jour la capture

[Drizzt [Drone38]]

Tu as pour certains NUMIMMO plusieurs CODESITE, ceci perturbe la requête ainsi construite qui attend un nombre précis de ligne par NUMIMMO.
(ex: NUMIMMO : 2987 et 4186)
Justement une ligne il trouve un PUK et pas l'AUTRE et il inverse le PIN avec l'IMEI. Donc je cmprend pas !

C'est normal que la requete fasse n'importe quoi, le fait d'avoir plusieurs CODESITE duplique les lignes du produit cartésien rim_act * rim_car associées. Ce n'est pas prévu en l'état.
Je réitère donc ma question en la complétant, est-ce normal d'avoir plusieurs CODESITE pour un meme NUMIMMO ?
Vu que tu n'as pas mis d'aggrégation dans ton select principal, je dirais que non.

J'ai mis à jour la capture

Au vu du résultat tu as plusieurs IMEI par NUMIMMO. Rien que pour le NUMIMMO 20 tu en as 8. Forcément la requête ne s'y attend pas.
La encore est-ce normal ?
La encore vu que tu sembles d'apres ton select n'attendre qu'un seul IMEI la question peut se poser.


Bref, au final on découvre des choses au fur et à mesure sur le modèle de donnée. Cela montre bien qu'on ne peut pas répondre correctement au problème sans avoir toutes les billes.

Donc avant de continuer plus loin, il faut que tu te poses les bonnes questions à savoir pour chaque champ peut-il y avoir plusieurs valeurs pour un NUMIMMO et si oui quelle valeur tu souhaites remonter.


Edit> Je me demande meme si ce n'est pas dans ta table rim_car que tu as plusieurs IMEI, vu que l'on en voit une en majuscule et les autres en minuscule...

[cbprogs]

exact. J'explique tout...

Ma table caractéristique est une table qui contient :
- imei
- pin
- puk
- codeImmo...

Ma table actif ne contient que des numero d'actif

Ma table possede est la relation entre les actifs et les caractéristriques.

Ma table site est une table qui contient des emplacement on dira ici des CODESITE.

Ma table mouvement est la relation entre les actif et un site.

REGLES
-un actif peut avoir autant de caractéristiques différentes possibles
-un actif ne peut avior q'un seul IMEI, un seul PIN un seul PUK et un seul CODEIMMO au max
- Sur un SITE il peut y avoir tant d'actif que l'on veut.

Je te pose mon MCD plus complet pour comprendre
http://cbprogs.free.fr/mcd.JPG

Est ce assez complet pour la compréhension ?
Dis moi car je en sais pas si je suis clair