Discussion :

[SISGI]

Bonjour qlq peut m'aider svp j'ai un problème lors de la creation d'un view voilà le code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CREATE  View DetailF as   SELECT C.IdFacture, C.NFacture, C.IdVille, C.DateFacture,C.Raisonsociale, C.IdClient, C.Adresse, C.TypeFacture, D.NBL, D.IdBL,C.IdDevise, C.TauxDevise, Code,  IdRepresentant,  NumFacture, Livre, Echeance,IdMode, N, Condition, FactureType, Commentaire1, Commentaire2, Commentaire3,Commentaire4, C.Acompte, C.Valide, D.IdArticle, D.Intitule, D.Qte, D.NbrRame,D.PrixU,D.DIMENSION, D.Remise, D.TauxTVA AS TVA, D.IdUnite, D.PMP,D.Poids,D.PoidsNet, D.Marge,round((case when D.dimension is null then 1 else D.dimension end),2) * round(Qte,2) as CumulQte,(round(D.Qte, 3) * round((case when A.PrixAchat is null then 0 else A.PrixAchat end), 3))  as TotalAchat,  (round(D.Qte, 3) * round(D.PMP, 2))  as TotalHTPMP,  Prix2,(CASE facturetype WHEN 'TTC' THEN (round(D.PrixU, 2) *(1 - (D.Remise / 100)) / (1 + (D.TauxTVA / 100))) ELSE ((1 - (D.Remise / 100)) * round(D.PrixU, 2)) END) AS Prix,(CASE facturetype WHEN 'TTC' THEN (round(D.Qte, 3) * round(D.PrixU, 2)*(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END)  * (1 - (D.Remise / 100)) / (1 + (D.TauxTVA / 100))) ELSE (round(D.Qte, 3)* (1 - (D.Remise / 100)) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END)* round(D.PrixU, 2)) END) AS TotalHT,(CASE WHEN (round(D.Qte, 3) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END)* round(D.PrixU, 2) * (D.Remise / 100)) IS NULL THEN 0 ELSE (round(D.Qte, 3) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END)* round(D.PrixU, 2) * round(D.Remise / 100, 2)) END) AS TotalRemise, (CASE facturetype WHEN 'TTC' THEN (round(D.Qte, 3) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END)* round(D.PrixU, 2) * (1 - (D.Remise / 100))) - (round(D.Qte, 3) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END)* round(D.PrixU, 2) * (1 - (D.Remise / 100)) /  (1 + (D.TauxTVA / 100))) ELSE (round(D.Qte, 3) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END)* round(D.PrixU, 2) * (1 - (D.Remise / 100)) * (D.TauxTVA / 100)) END) AS TotalTVA,  round((CASE facturetype WHEN 'TTC' THEN (round(D.Qte, 3)* round(D.PrixU, 2) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END) * (1 - (D.Remise / 100)) / (1 + (D.TauxTVA / 100))) ELSE (round(D.Qte, 3) * (1 - (D.Remise / 100)) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END)* round(D.PrixU, 2)) END), 2) + round((CASE facturetype WHEN 'TTC' THEN (round(D.Qte, 3) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END)* round(D.PrixU, 2) * (1 - (D.Remise / 100))) - (round(D.Qte, 3) * round(D.PrixU, 2) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END)* (1 - (D.Remise / 100)) / (1 + (D.TauxTVA / 100))) ELSE (round(D.Qte, 3) * round(D.PrixU, 2) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END)* (1 - (D.Remise / 100)) * (D.TauxTVA / 100)) END), 2) AS TotalTTC, (CASE WHEN D.TauxTVA=20 Then (CASE facturetype WHEN 'TTC' THEN (round(D.Qte, 3) * round(D.PrixU, 2) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END)* (1 - (D.Remise / 100)) / (1 + (D.TauxTVA / 100))) ELSE (round(D.Qte, 3) * (1 - (D.Remise / 100)) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END)* round(D.PrixU, 2)) END) else 0  end) AS TotalHT20,  (CASE WHEN D.TauxTVA=7 Then (CASE facturetype WHEN 'TTC' THEN (round(D.Qte, 3) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END)* round(D.PrixU, 2) * (1 - (D.Remise / 100)) / (1 + (D.TauxTVA / 100))) ELSE (round(D.Qte, 3) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END)* (1 - (D.Remise / 100)) * round(D.PrixU, 2)) END) else 0  end) AS TotalHT7,(CASE WHEN D.TauxTVA=10 Then (CASE facturetype WHEN 'TTC' THEN (round(D.Qte, 3) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END)* round(D.PrixU, 2) * (1 - (D.Remise / 100)) / (1 + (D.TauxTVA / 100))) ELSE (round(D.Qte, 3) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END)* (1 - (D.Remise / 100)) * round(D.PrixU, 2)) END) else 0  end) AS TotalHT10,(CASE WHEN D.TauxTVA=14 Then (CASE facturetype WHEN 'TTC' THEN (round(D.Qte, 3) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END) * round(D.PrixU, 2) *(1 - (D.Remise / 100)) / (1 + (D.TauxTVA / 100))) ELSE (round(D.Qte, 3) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END) * (1 - (D.Remise / 100)) * round(D.PrixU, 2)) END) else 0  end) AS TotalHT14, (CASE when D.TauxTVA=0 then (round(D.Qte, 3)* round(D.PrixU, 2) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END) * (1 - (D.Remise / 100)))  else 0 end) AS TotalHT0,  (CASE WHEN D.TauxTVA=20 then (CASE facturetype WHEN 'TTC' THEN (round(D.Qte, 3) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END) * round(D.PrixU, 2) * (1 - (D.Remise / 100))) - (round(D.Qte, 3) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END) * round(D.PrixU, 2) * (1 - (D.Remise / 100)) / (1 + (D.TauxTVA / 100))) ELSE (round(D.Qte, 3) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END) * round(D.PrixU, 2) * (1 - (D.Remise / 100)) * (D.TauxTVA / 100)) END) else 0 end) AS TotalTVA20,(CASE WHEN D.TauxTVA=7 then (CASE facturetype WHEN 'TTC' THEN (round(D.Qte, 3)* round(D.PrixU, 2) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END) * (1 - (D.Remise / 100))) - (round(D.Qte, 3) * round(D.PrixU, 2) * (1 - (D.Remise / 100)) / (1 + (D.TauxTVA / 100))) ELSE (round(D.Qte, 3) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END)* round(D.PrixU, 2) * (1 - (D.Remise / 100)) * (D.TauxTVA / 100)) END) else 0 end) AS TotalTVA7,(CASE WHEN D.TauxTVA=10 then (CASE facturetype WHEN 'TTC' THEN (round(D.Qte, 3)* round(D.PrixU, 2) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END) * (1 - (D.Remise / 100))) - (round(D.Qte, 3) * round(D.PrixU, 2) * (1 - (D.Remise / 100)) / (1 + (D.TauxTVA / 100))) ELSE (round(D.Qte, 3) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END)* round(D.PrixU, 2)* (1 - (D.Remise / 100)) * (D.TauxTVA / 100)) END) else 0 end) AS TotalTVA10,(CASE WHEN D.TauxTVA=14 then (CASE facturetype WHEN 'TTC' THEN (round(D.Qte, 3)* round(D.PrixU, 2) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END) * (1 - (D.Remise / 100))) - (round(D.Qte, 3) * round(D.PrixU, 2) * (1 - (D.Remise / 100)) / (1 + (D.TauxTVA / 100))) ELSE (round(D.Qte, 3)* round(D.PrixU, 2) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END) * (1 - (D.Remise / 100)) * (D.TauxTVA / 100)) END) else 0 end) AS TotalTVA14,  (CASE facturetype WHEN 'TTC' THEN (round(D.Qte, 3) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END) * round(D.PrixU, 2)*TauxDevise * (1 - (D.Remise / 100)) / (1 + (D.TauxTVA / 100))) ELSE (round(D.Qte, 3) * (1 - (D.Remise / 100)) * round(D.PrixU, 2)*TauxDevise) END)AS TotalHTDevise,  (CASE WHEN (round(D.Qte, 3) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END)* round(D.PrixU, 2)*TauxDevise * (D.Remise / 100)) IS NULL THEN  0 ELSE (round(D.Qte, 3) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END) * round(D.PrixU, 2)*TauxDevise * round(D.Remise / 100, 2)) END)  AS TotalRemiseDevise, (CASE facturetype WHEN 'TTC' THEN (round(D.Qte, 3) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END) * round(D.PrixU, 2)*TauxDevise * (1 - (D.Remise / 100))) - (round(D.Qte, 3) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END) * round(D.PrixU, 2)*TauxDevise * (1 - (D.Remise / 100)) / (1 + (D.TauxTVA / 100))) ELSE (round(D.Qte, 3) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END) * round(D.PrixU, 2)*TauxDevise * (1 - (D.Remise / 100)) * (D.TauxTVA / 100)) END) AS TotalTVADevise, round((CASE facturetype WHEN 'TTC' THEN (round(D.Qte, 3) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END) * round(D.PrixU, 2)*TauxDevise * (1 - (D.Remise / 100)) / (1 + (D.TauxTVA / 100))) ELSE (round(D.Qte, 3) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END) * (1 - (D.Remise / 100)) * round(D.PrixU, 2)*TauxDevise) END), 2) + round((CASE facturetype WHEN 'TTC' THEN (round(D.Qte, 3)* round(D.PrixU, 2)*(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END) * TauxDevise * (1 - (D.Remise / 100))) -  (round(D.Qte, 3) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END) * round(D.PrixU, 2)*TauxDevise * (1 - (D.Remise / 100)) / (1 + (D.TauxTVA / 100))) ELSE (round(D.Qte, 3) *(CASE WHEN D.DIMENSION IS NULL THEN 1 ELSE D.DIMENSION END) * round(D.PrixU, 2)*TauxDevise * (1 - (D.Remise / 100)) * (D.TauxTVA / 100)) END), 2) AS TotalTTCDevise,  D.NOrder , D.NLigne , C.NLigne as NumLigne, B.NDocument as CMD  FROM FacturesClients AS C INNER JOIN ((FacturesArticlesClients AS D left join BLClients AS B on B.Nfacture=D.NBL) left join Articles as A on A.IdArticle=D.IdArticle) ON C.IdFacture = D.IdFacture

et voilà les erreurs qui m'affiche
'Code' : nom de colonne ambigu.
et la mem erreur pour tous les champs mercii d'avance

[aieeeuuuuu]

Bonjour,

Si plusieurs colonnes portent le même nom dans des tables différentes, alors vous devez spécifier le nom de la table avant le nom de colonne, pour que le moteur sache de quelle colonne il s'agit :
Si vos noms de table sont trop longs/compliqués, vous pouvez leur donner des alias :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
SELECT 
    T1.Code,
    T2.Code
FROM UneTableQuiContientUneColonneCode T1
INNER JOIN UneAutreTableQuiContientAussiUneColonneCode T2
    ON T2.id = T1.refId

[alassanediakite]

Salut
Fais accompagner tous le champ du nom de la table correspondantes. Comme ...tableX.champY. Vous devez comprendre maintenant pourquoi l'AMBIGUITE
@+

[SISGI]

oui exactement merciii bc