Avis aux experts en SQL

Avis aux experts en SQL - SQL/NoSQL - Programmation

Marsh Posté le 18-06-2009 à 10:46:28    

Bonjour tout le monde,
 
Ca fait 2 mois que je galère avec cette requête, je l'ai posté dans plein de forums et .... personne n'a su encore trouvé de solutions...
 
Donc s'il vous plaît j'ai besoin de votre aide.
 
Le but de ma requête est de fusionner les lignes qui ont (nucdli = nu_document) sur les lignes qui ont  (nu_document) , d'additionner une de leur colonne : leur prix (px_commande).
 
Le problème vient que ma requête ne me renvoie pas toutes les lignes.  
 
Voilà : Avis aux amateurs !
 

Code :
  1. SELECT /* Selection sur l'ensemble de la requete (prise en compte des paramètres et ordre de tri)      et des sous requetes */
  2.   A.id_produit,
  3.   A.id_fournisseur,
  4.   A.ty_document,
  5.   A.nu_document,
  6.   A.dt_document,
  7.   A.qt_cmde_ini ,
  8.   A.px_commande,
  9.   A.ty_commande,
  10. (A.px_commande*A.qt_cmde_ini) AS Valorisation,
  11.   A.id_user,
  12.   B.li_raison_sociale,
  13.   C.li_produit,
  14.   C.id_famille_produit,
  15.   F.li_couleur,
  16.   F.li_marquage,
  17.   F.li_composition
  18. FROM
  19. (SELECT /* SOUS REQUETE 1. Vise à faire un calcul (px_commande)
  20.             sur l'ensemble des données extraites ds Q */
  21.   Q.id_produit,
  22.   Q.id_fournisseur,
  23.   Q.dt_document,
  24.   Q.qt_cmde_ini,
  25.   Q.ty_commande,
  26.   Q.id_societe,
  27.   Q.id_offre,
  28.   Q.ty_document,
  29.   Q.nu_document,
  30.   Q.nucdli,
  31.   Q.id_user,
  32.   q.dt_livraison,
  33.   q.dt_confirmation,
  34.   Sum(q.px_commande) AS px_commande /* Calcul de la somme*/
  36.     FROM
  37.       ( SELECT /* SOUS REQUETE 2. Extraction des données agrégées par CDA*/
  38.        z.id_produit,
  39.        z.id_fournisseur,
  40.        z.dt_document,
  41.        z.qt_cmde_ini,
  42.        z.ty_commande,
  43.        z.id_societe,
  44.        z.id_offre,
  45.        z.id_user,
  46.        z.dt_livraison,
  47.        z.dt_confirmation,
  48.                    CASE z.cde_ter /*Dans le cas de cde ter*/
  49.                    WHEN 0 THEN z.ty_document /* Si cde ter = 0 ( quand ty_document <> CDA alors        on récupère ty_document*/
  50.                    ELSE 'CDA' /* Sinon on récupère CDA*/
  51.                    END AS ty_document, /*Tout ceci est dans ty_document*/
  53.                    CASE z.cde_ter
  54.                    WHEN 1 THEN NULL /* Si ty_document = CDA * alors on récupère rien */
  55.                    ELSE z.nucdli /*Sinon on récupère z.nucdli*/
  56.                    END AS nucdli, /*tout ceci est dans nucdli, z.px_commande, z.cde_ter*/
  57.                    z.px_commande,
  58.                    z.cde_ter,
  60.                    CASE z.cde_ter
  61.                    WHEN 1 THEN z.nucdli /* Si ty_document = CDA alors on écrit rien z.nucdli */
  62.                    ELSE z.nu_document /* Sinon on récupère nu_document */
  63.                    END AS nu_document  /* Tout ceci est dans nu_document */
  66.          FROM             (SELECT *,
  67.      /* SOUS REQUETE 3. Agrégation des données dans le cas où nucdli =     nu_commande */
  68.                                 CASE
  69.                                 WHEN EXISTS (SELECT 1 /* SOUS REQUETE 4. Extraction des données où         nucdli = nu_commande  et ty_commande = CDA */
  70.                                         FROM   tbl_ligneachat h
  71.                                         WHERE  h.id_produit = k.id_produit
  72.                                         AND k.nucdli = h.nu_document
  73.                                         AND h.ty_document = 'CDA') /* FIN SOUS REQUETE 4 */
  74.                                 THEN 1
  75.                                 ELSE 0
  76.                                 END AS cde_ter
  78.                            FROM   tbl_ligneachat k
  80.                             )AS z /* FIN SOUS REQUETE 3 */
  82.         ) AS Q /* FIN SOUS REQUETE 2 */
  84.      GROUP BY /* Groupement effectué pour le calcul de px_commande */
  85.        q.id_produit,
  86.        q.qt_cmde_ini,
  87.        q.ty_commande,
  88.        q.nu_document,
  89.        q.nucdli,
  90.        q.id_societe,
  91.        q.id_offre
  93. )AS A /* FIN SOUS REQUETE 1 */
  94.  
  96.   LEFT OUTER JOIN tbl_fichetech AS F /* Jointure de A sur F et E*/
  97.       ON A.id_societe = F.id_societe
  98.       AND A.id_produit = F.id_produit
  99.   LEFT OUTER JOIN tbl_offre AS E
  100.       ON A.id_societe = E.id_societe
  101.       AND A.id_offre = E.id_offre,
  103.   tbl_fournisseur AS B,
  105.   tbl_produit AS C
  107.   WHERE
  109.   A.id_societe = B.id_societe and A.id_fournisseur = B.id_fournisseur
  110.   and A.id_societe = C.id_societe and A.id_produit = C.id_produit
  111.   and C.id_sousfamille_produit between $P{SousFamilleD} and $P{SousFamilleF}
  112.   and C.id_division_produit between $P{DivisionD} and $P{DivisionF}
  113.   and A.id_produit between $P{CodeProduitD} and $P{CodeProduitF}
  114.   and B.li_raison_sociale between $P{FournisseurD} and $P{FournisseurF}
  115.   and C.id_categorie_produit between $P{CategorieD} and $P{CategorieF}
  116.   and C.id_famille_produit between $P{FamilleD} and $P{FamilleF}
  117.   and A.id_user between $P{CodeUtilisateurD} and $P{CodeUtilisateurF}
  118.   and ifnull(A.ty_commande,"" ) between $P{TypeCommandeD} and $P{TypeCommandeF}
  119.   and ifnull(E.id_offre,"" ) between $P{OffreD} and $P{OffreF}
  120.   and ifnull(E.ty_offre,"" ) between $P{TypeOffreD} and $P{TypeOffreF}
  121.   and
  122.    case
  123.     when A.ty_document = "DMA" then A.dt_document between $P{DateLivraisonD} and $P{DateLivraisonF}
  124.     when A.ty_document = "BE" then A.dt_document between $P{DateLivraisonD} and $P{DateLivraisonF}
  125.     when A.ty_document = "CDA" then ifnull(A.dt_confirmation,"1900-01-01" ) between $P{DateLivraisonD} and $P{DateLivraisonF}
  126.     else if(A.dt_livraison="0000-00-00","1900-01-01",A.dt_livraison) between $P{DateLivraisonD} and $P{DateLivraisonF}
  127.     end
  129. ORDER BY
  130.   C.id_famille_produit,
  131.   B.li_raison_sociale,
  132.   A.id_produit,
  133.   A.ty_document;


 

Message cité 1 fois
Reply

Marsh Posté le 18-06-2009 à 10:46:28   

Reply

Marsh Posté le 18-06-2009 à 19:45:57    

Euh, va falloir la refaire au ralenti là, parce que je vois pas comment à partir d'une requête aussi imbittable, sans explication du modèle, on arriverait à donner une réponse à une question aussi vague ;)
 
Est-ce que tu peux expliquer ce que c'est sensé faire, ce que ça fait, et où à priori ça déconne ?

Reply

Marsh Posté le 19-06-2009 à 09:30:17    

allodren a écrit :


Le problème vient que ma requête ne me renvoie pas toutes les lignes.  


C'est généralement le cas quand une requête ne marche pas. Donc en gros, ta requête ne marche pas, et tu espères avoir une solution avec le peu d'infos que tu fournis ?
Ca m'étonne pas que ça fasse 2 mois que tu n'aies aucune réponse
 

allodren a écrit :


 
Voilà : Avis aux amateurs !


Je doute fort que tu trouves des amateurs à la boule de cristal si évoluée qu'elle leur permette de deviner le modèle de ta base

Reply

Marsh Posté le 19-06-2009 à 10:23:59    

Bonjour,  
 
Merci pour votre attention.
 
Le but est de calculer un tarif total  
 
J'ai une table ACHAT avec id_prod = produit
ty_doc =type de document
nu_doc=numéro de document
nucdli = commande liée
px_com= prix de la commande
 
id_prod____ty_doc___ nu_doc___ nucdli___px_com
A___________ BE ____ 13604_____________ 4
B____________ BE ____ 13604 _____________ 4
C_______ ___ CDA ___ 13604 _____________ 3
D_______ ___ CDA ___ 13604 _____________ 3
E_______ ___ CDA ___ 13604 ____________ 2
E ______ ___ CDC ___ 13661 ___ 13604 ___ 3
B ______ ___ CDC ___ 13361 ___ 13604 ___ 2
E ______ ___ CDT ___ 13360 ___ 13604 ___ 1
 
Mon problème est le suivant :
 
Je souhaite afficher dans le px_com de CDA le cumul des px_com des CDD et des CDT correspondants (quand "nucdli"="nu_doc" )
Du coup, les CDD et CDT correspondantes au CDA doivent disparaitrent !
 
Au final on doit obtenir  
 
id_prod____ty_doc___ nu_doc___ nucdli___px_com
E___________ CDA ____13604 _____________6
B____________ BE ____ 13604 _____________ 4
C_______ ___ CDA ___ 13604 _____________ 3
D_______ ___ CDA ___ 13604 _____________ 3
B ______ ___ CDC ___ 13361 ___ 13604 ____ 2
 
Voici un jeu de test :
 

Code :
  1. CREATE TABLE tbl_ligneachat (id_prod char(1), ty_doc char(3), nu_doc int , nucdli int NULL, px_com int NULL)
  2. INSERT INTO tbl_ligneachat VALUES ('A','BE',13604,NULL,4)
  3. INSERT INTO tbl_ligneachat VALUES ('B','BE',13604,4)
  4. INSERT INTO tbl_ligneachat VALUES ('C','CDA',13604,NULL,3)
  5. INSERT INTO tbl_ligneachat VALUES ('D','CDA',13604,NULL,3)
  6. INSERT INTO tbl_ligneachat VALUES ('E','CDA',13604,NULL,2)
  7. INSERT INTO tbl_ligneachat VALUES ('E','CDC',13661,13604,3)
  8. INSERT INTO tbl_ligneachat VALUES ('B','CDC',13361,13604,2)
  9. INSERT INTO tbl_ligneachat VALUES ('E','CDT',13360,13604,1)
  10. INSERT INTO tbl_ligneachat VALUES ('E','FA',444,NULL,1)
  11. INSERT INTO tbl_ligneachat VALUES ('E','BE','555',NULL,1)


 
Mais prenons la première partie de la requête, sans le WHERE : C'est à l'intérieur que se situe le problème :
 

Code :
  1. SELECT
  2.   A.id_produit,
  3.   A.id_fournisseur,
  4.   A.ty_document,
  5.   A.nu_document,
  6.   A.qt_cmde_ini ,
  7.   A.px_commande,
  8.   A.ty_commande,
  9.   (A.px_commande*A.qt_cmde_ini) AS Valorisation,
  10.   A.id_user
  12. /* Calcul de la somme*/
  13. FROM(
  14. SELECT
  16.   k.id_produit,
  17.   k.id_fournisseur,
  18.   k.dt_document,
  19.   k.qt_cmde_ini,
  20.   k.ty_commande,
  21.   k.id_societe,
  22.   k.id_offre,
  23.   k.ty_document,
  24.   k.nu_document,
  25.   k.nucdli,
  26.   k.id_user,
  27.   k.dt_livraison,
  28.   k.dt_confirmation,
  29.   sum(k.px_commande) AS px_commande /* Calcul de la somme*/
  31.    FROM(
  32.        SELECT /* SOUS REQUETE 2. Extraction des données agrégées par CDA*/
  33.        z.id_produit,
  34.        z.id_fournisseur,
  35.        z.dt_document,
  36.        z.qt_cmde_ini,
  37.        z.ty_commande,
  38.        z.id_societe,
  39.        z.id_offre,
  40.        z.id_user,
  41.        z.dt_livraison,
  42.        z.dt_confirmation,
  43.        z.px_commande,
  45.                           CASE z.cde_ter /*Dans le cas de cde ter*/
  46.                    WHEN 0 THEN z.ty_document /* Si cde ter = 0 ( quand ty_document <> CDA alors  on récupère ty_document*/
  47.                    ELSE 'CDA' /* Sinon on récupère CDA*/
  48.                    END AS ty_document, /*Tout ceci est dans ty_document*/
  50.                    CASE z.cde_ter
  51.                    WHEN 1 THEN NULL /* Si ty_document = CDA * alors on récupère rien */
  52.                    ELSE z.nucdli /*Sinon on récupère z.nucdli*/
  53.                    END AS nucdli, /*tout ceci est dans nucdli, z.px_commande, z.cde_ter*/
  54.                     z.cde_ter,
  56.                    CASE z.cde_ter
  57.                    WHEN 1 THEN z.nucdli /* Si ty_document = CDA alors on écrit rien z.nucdli */
  58.                    ELSE z.nu_document /* Sinon on récupère nu_document */
  59.                    END AS nu_document  /* Tout ceci est dans nu_document */
  62.            FROM  (SELECT *,
  63.      /* SOUS REQUETE 3. Agrégation des données dans le cas où nucdli =     nu_commande */
  64.                                 CASE
  65.                                 WHEN EXISTS (SELECT 1 /* SOUS REQUETE 4. Extraction des données où nucdli = nu_commande  et ty_commande = CDA */
  66.                                         FROM   tbl_ligneachat h
  67.                                         WHERE  h.id_produit = q.id_produit
  68.                                         AND q.nucdli = h.nu_document
  69.                                         AND h.ty_document = 'CDA') /* FIN SOUS REQUETE 4 */
  70.                                 THEN 1
  71.                                 ELSE 0
  72.                                 END AS cde_ter
  74.                            FROM   tbl_ligneachat Q
  75.            )AS z /* FIN SOUS REQUETE 3 */
  77.    )AS k
  79. GROUP BY
  80.        k.id_produit,
  81.        k.qt_cmde_ini,
  82.        k.ty_commande,
  83.        k.nu_document,
  84.        k.nucdli,
  85.        k.id_societe,
  86.        k.id_offre
  87. )AS A


 
Cette requête ne me renvoi pas ttes les lignes ...
 
Si je retire mon SUM la requête me renvoi bien toutes les lignes ...
Si je mets dans mon " GROUP BY" ttes les critères, cela me renvoi toutes les lignes mais ne me calcule pas la somme.
 
___On groupe toujours par au moins tous les éléments de la sélection___
 
Mais dans ce cas : cela ne me calcule pas correctement mon besoin qui est :
Addition des valeurs "px_commande" pour toutes les lignes de ma table qui ont : nucdli = nu_document.
On affiche la somme dans le px_commande de la ligne où se trouve le "nu_document" identique.
Et on ne retient plus la ligne où se trouve le "nucdli" lié.
 
Ex :
 
Ma table actuelle est
 
id_prod | ty_document | nu_document | nucdli | px_commande
 
A | CDA | 160 | |2
A| CDD | 161 | 160 | 2
A | CDA | 162 | | 2
 
Je souhaite l' extraire tel que :
 
A|CDA|160| |4  
A|CDA|162||2
 
Mon premier résultat est l'addition entre la première et seconde ligne qui ont nucdli = nu_document.
 
 
---> RÉCAPITULONS:
 
Dans un premier temps, cette requête regroupe les "nucdli"  
(n° commandes liées) et les "nu_document" ( n ° commande ) qui ont les mêmes identifiants ( 2 colonnes dans une même table qui ont les mêmes identifiants).  
 
Dans un second temps, elle est censée additionner les "px_commande" ( le coût des commandes) des regroupements ( au total, il y a 8 regroupement sur 40 664 lignes ).
 
Étant donnée que ma requête fusionne les "nucdli" et les "nu_document" qui ont les mêmes identifiants, et qu'il y en a 8, la requête devrait me retourner 40 656 lignes.
Or ma requête fait bien la somme et effectue bien la fusion mais ne me renvoi que 40 546 lignes soit une centaine en moins !
 
Je suis pleinement d'accord avec vous mais je ne dois perdre que 8 lignes (puisqu'il y a 8 ligne où nucdli = nu_document) et non pas 101 !
 
Lorsque j'ôte cette somme de la requête : J'ai bien toutes mes lignes - 8.
 
Donc j'ai identifié le calcul comme source de ce problème cornélien.
 
___________________________________________________________________________________________
 
Après analyse des groupes  
 
Je me suis rendu compte que lorsque je fais mon calcul, toutes les nu_document qui étaient en double, pour un produit, n'étaient pas pris en compte qu'une seule fois ....
 
EX :
 
id_prod | nu_doc
 
100|160
100|160
100|150
 
La requête ne me retourne que {100;160 et 100;150}
 
Voila mes cent lignes manquantes.
 
Comment les rapatriés ?  
 
J'ai trouver comment les rapatrier mais cela ne me fait pas le calcul de somme :
 
Code :
 

Code :
  1. SELECT
  2.   k.id_produit,
  3.   k.id_fournisseur,
  4.   k.dt_document,
  5.   k.qt_cmde_ini,
  6.   k.ty_commande,
  7.   k.id_societe,
  8.   k.id_offre,
  9.   k.ty_document,
  10.   k.nu_document,
  11.   k.id_user,
  12.   k.dt_livraison,
  13.   k.dt_confirmation,
  14.   k.px_commande + coalesce(k3.px_commande,0) AS px_commande
  16. FROM
  17.   tbl_ligneachat k
  19.            LEFT OUTER JOIN (
  21. SELECT
  22.   k2.id_produit,
  23.   k2.id_fournisseur,
  24.   k2.dt_document,
  25.   k2.qt_cmde_ini,
  26.   k2.ty_commande,
  27.   k2.id_societe,
  28.   k2.id_offre,
  29.   k2.ty_document,
  30.   k2.nu_document,
  31.   k2.id_user,
  32.   k2.dt_livraison,
  33.   k2.dt_confirmation,
  34.   k2.px_commande,
  35.   k2.nucdli
  37.    FROM tbl_ligneachat K2) AS K3
  41.          ON  K3.nucdli IS NOT NULL AND k3.nucdli=k.nu_document
  43. WHERE K.nucdli IS NULL
  45.          ORDER BY id_produit


 
 
Cette requête me renvoie toutes les lignes.
 
Cependant, cela ne m'additionne pas les lignes qui ont un nucdli=nu_document
 
Toujours le même problème.
 
J'ai essayé de tourner le code ci-après de différentes façons, je présente la version la plus cohérente.  
 
 
J'espère que j'ai été assez clair...

Reply

Sujets relatifs:

Leave a Replay

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