0 Replies Latest reply: Nov 4, 2014 8:03 AM by SOULEIMANE BELARIBI RSS

    field calculated from two tables or more

      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