Hi,
I would like to créate a news columns from 2 tables:
CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN -L.LIG_QTE ELSE L.LIG_QTE END AS 'Qte',
CASE WHEN (D.DOC_STYPE) IN ('A', '0', 'R') THEN -(L.[LIG_QTE] * L.LIG_POIDSN) ELSE L.[LIG_QTE] * L.LIG_POIDSN END AS 'Poids',
Is this code is correct?
DIM_DOCUMENTS :
LOAD Month (DOC_DATE) as Mois,
Year ("DOC_DATE") as Année,
date ("DOC_DATE") as Date,
"DOC_TYPE",
"DOC_STYPE",
If(DOC_TYPE = 'A', 'Achat', if(DOC_TYPE = 'V', 'Ventes')) as [Type de doc.],
/*Sous types de documents*/
If(DOC_TYPE = 'V',
if(DOC_STYPE = 'P', 'Pro-forma',
if(DOC_STYPE = 'D', 'Devis',
if(DOC_STYPE = 'C', 'Commandes',
if(DOC_STYPE = 'B', 'Bons de Livraison',
if(DOC_STYPE = 'R', 'Bons de Retour',
if(DOC_STYPE = 'F', 'Factures',
if(DOC_STYPE = '1', 'Factures Financières',
if(DOC_STYPE = 'A', 'Avoirs',
if(DOC_STYPE = '0', 'Avoirs Financiers',
))))))))),
if(DOC_TYPE = 'A',
if(DOC_STYPE = 'D', 'Demandes de Prix',
if(DOC_STYPE = 'C', 'Commandes',
if(DOC_STYPE = 'B', 'Bons de Réceptions',
if(DOC_STYPE = 'R', 'Bons de Retour',
if(DOC_STYPE = 'F', 'Factures',
if(DOC_STYPE = '1', 'Factures Financières',
if(DOC_STYPE = 'A', 'Avoirs',
if(DOC_STYPE = '0', 'Avoirs Financiers')
))))))))
) as [Sous type de doc.],
"DEP_CODE",
"DEV_CODE",
"DEV_CODE",
"DIV_CODE",
"DOC_DT_PRV",
"DOC_EN_TTC",
"DOC_ETAT",
"DOC_NUMERO";
SQL SELECT DOC_DATE,
'[Sous type de doc.]',
DEP_CODE,
DEV_CODE,
DEV_CODE,
DIV_CODE,
DOC_DT_PRV,
DOC_EN_TTC,
DOC_ETAT,
DOC_NUMERO
FROM ICP.dbo.DOCUMENTS;
Join Load
DOC_NUMERO,
LIG_QTE
From ICP.dbo.LIGNES
Result:
LOAD *,
If(Match(DOC_STYPE ,'A', '0', 'R'),-(LIG_QTE),LIG_QTE) as 'Qte',
If(Match(DOC_STYPE ,'A', '0', 'R'),-(LIG_QTE * L.LIG_POIDSN),(LIG_QTE * L.LIG_POIDSN)) as 'Poids',
If(Match(DOC_STYPE ,'A', '0', 'R'),-(LIG_FRAIS * LIG_QTE),LIG_FRAIS * LIG_QTE) as 'Frais1'
Resident ;
drop table DIM_DOCUMENTS;
regards
