field calculated from two tables or more

Tue, 04 Nov 2014 13:03:40 GMT

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",

"DIV_CODE",

"DOC_DT_PRV",

"DOC_EN_TTC",

"DOC_ETAT",

"DOC_NUMERO";

SQL SELECT DOC_DATE,

'[Sous type de doc.]',

DEP_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

topic field calculated from two tables or more in Francophones

field calculated from two tables or more