3 Replies Latest reply: Sep 14, 2011 3:30 AM by Jérôme VANHEULE RSS

    How to join more than 2 tables

      Hi,

      This is my problem.

      I have a fact table named CMOUVEDT.

      I have another table which contains some lookup data : CACCOUTB.

       

      I want to extract each lookup in simple data tables:

       

      This is the script :

       

      CMOUVEDT :
      LOAD       MNGSOCCD,
                 MNGPOSCD,
                 DCEXPRCD as PERIODE,
                 DOCPERCD as MOIS,
                 DOCEXECD as ANNEE,
                 JRNCODCD as JOURNAL,
                 DOCNUMCD as PIECE,
                 DOCLINCD as LIGNE,
                 DOCDATCD, 
                 JRNTYPCD as JOURNAL_TYPE,
                 GENACCCD as COMPTE,
                 CRLCCUCD - DELCCUCD as MONTANT,
                 ANAACCCD,
                 FLRREFCD,
                 COMNUMCD,
                 SALNBRCD,
                 PURNBRCD,
                 MVTDSCCD as DESCRIPTION,
                 CREUSRCD,
                 CREDATCD,
                 UPTUSRCD,
                 UPTDATCD 
                      ;
      SQL SELECT MNGSOCCD,
                 MNGPOSCD,
                 DCEXPRCD,
                 DOCPERCD,
                 DOCEXECD,
                 JRNCODCD,
                 DOCNUMCD,
                 DOCLINCD,
                 DOCDATCD, 
                 JRNTYPCD,
                 GENACCCD,
                 DELCCUCD ,
                 CRLCCUCD,
                 ANAACCCD,
                 FLRREFCD,
                 COMNUMCD,
                 SALNBRCD,
                 PURNBRCD,
                 MVTDSCCD,
                 CREUSRCD,
                 CREDATCD,
                 UPTUSRCD,
                 UPTDATCD 
                 from 
                 CMOUVEDT 
      where GENACCCD between 600000000 and 799999999 And MNGSOCCD = 1 AND MNGPOSCD in (1,2);;
      ;
      
      //*
      LU_FOURNISSEURS :
      LOAD * ;
      SQL select COMNUMCP as COMNUMCD
                 ,COMALPCP as CODE_FOURNISSEUR,
                  COMNA1CP as FOURNISSEUR
        from ECOMPYTB where MNGSOCCP = '001'
      
      ;;
      LU_PRODUITS : 
      LOAD * ;
      SQL select ACCCODAC as  ANAACCCD
      //,ACCDS1AC
      ,ACCALPAC  as PRODUIT
          from CACCOUTB where ACCTYPAC = 'ANA' and MNGSOCAC = '001' and MNGPOSAC = '002'
      ;;    
      LU_DOSSIERS :
      LOAD * ;
      SQL SELECT ACCCODAC as FLRREFCD
         ,ACCALPAC  as DOSSIER
          from CACCOUTB where ACCTYPAC = 'FLD' and MNGSOCAC = '001' and MNGPOSAC = '002'
      ;;    
      LU_COMPTES : 
      LOAD *;
      SQL select ACCCODAC as GENACCCD
                ,ACCDS1AC as COMPTE_LIB,
                ACCALPAC,
                LE8001AC as COMPTE_GR1,
                LE8002AC as COMPTE_GR2,
                LE8003AC as COMPTE_GR3,
                LE8004AC as COMPTE_GR4,
                LE8005AC as COMPTE_GR5 
          from CACCOUTB where ACCTYPAC = 'GEN' and MNGSOCAC = '001' and MNGPOSAC = '000' 
      ;;
      LU_PROJETS : 
      LOAD * ;
      SQL  select NBRPRJPD as PURNBRCD
            ,ACCALPPD as PROJET,DESCR1PD  
           from EPRDETDT where MNGSOCPD = '001'and MNGPOSPD = '002' 
      ;;
      lu_services :
       
      SQL select CONNUMCN as SALNBRCD
           ,CONNA1CN as SERVICE
       from ECONTATB where MNGSOCCN = '001' and MNGPOSCN = '002'  
       ;;
      
      


      So, I would like create another table named COMPTA, that "left join" CMOUVEDT with lookup table.

       

       

       

      compta :
      load * resident CMOUVEDT;
      left join LOAD * Resident LU_COMPTES ;
      left join LOAD * Resident LU_DOSSIERS;
      left join load * resident LU_PRODUITS;
      left join load * resident LU_FOURNISSEURS;
      left join load * resident LU_SERVICES;
      left join load * resident LU_PROJETS;
      
      


      But the table named Compta is not created.

      Why creating this table ? Because a need scan it later : i.e : select * from compta where .....

       

      What the good way to do this ?

       

      Thanks in advance.

      Jérôme.

        • How to join more than 2 tables
          Sunil Chauhan

          compta :

          load * resident CMOUVEDT;

          left join(Tablenametowhichlef join) LOAD * Resident LU_COMPTES ;

          left join(Tablenametowhichlef join)  LOAD * Resident LU_DOSSIERS;

          left join(Tablenametowhichlef join)  load * resident LU_PRODUITS;

          left join(Tablenametowhichlef join)  load * resident LU_FOURNISSEURS;

          left join(Tablenametowhichlef join)  load * resident LU_SERVICES;

          left join(Tablenametowhichlef join)  load * resident LU_PROJETS;

          • Re: How to join more than 2 tables
            Goran Korsgren

            Hi jvan

             

            Compta is not created because it contains exactly the same fields as CMOUVEDT.

             

            The default behaviour of QV is in that case to concatenate the data to the already existing table.

             

             

            Use Noconcatenate Load like this:

             

            Compta:

            Noconcatenate Load *

            Resident CMOUVEDT;

             

             

            hth/gg

            • Re: How to join more than 2 tables

              Thanks all.

               

              so I resolved my problem like this :

              COMPTA :
              LOAD      
               MNGSOCCD,
                         MNGPOSCD,
                         DCEXPRCD as PERIODE,
                         DOCPERCD as MOIS,
                         DOCEXECD as ANNEE,
                         JRNCODCD as JOURNAL,
                         DOCNUMCD as PIECE,
                         DOCLINCD as LIGNE,
                         DOCDATCD, 
                         JRNTYPCD as JOURNAL_TYPE,
                           GENACCCD as COMPTE,
                         CRLCCUCD - DELCCUCD as MONTANT,
                         ANAACCCD,
                         FLRREFCD,
                           COMNUMCD,
                         SALNBRCD,
                         PURNBRCD,
                         MVTDSCCD as DESCRIPTION,
                         CREUSRCD,
                         CREDATCD,
                         UPTUSRCD,
                         UPTDATCD 
                              ;
              SQL SELECT MNGSOCCD,
                         MNGPOSCD,
                         DCEXPRCD,
                         DOCPERCD,
                         DOCEXECD,
                         JRNCODCD,
                         DOCNUMCD,
                         DOCLINCD,
                         DOCDATCD, 
                         JRNTYPCD,
                         GENACCCD,
                         DELCCUCD ,
                         CRLCCUCD,
                         ANAACCCD,
                         FLRREFCD,
                         COMNUMCD,
                         SALNBRCD,
                         PURNBRCD,
                         MVTDSCCD,
                         CREUSRCD,
                         CREDATCD,
                         UPTUSRCD,
                         UPTDATCD 
                         from 
                         CMOUVEDT 
              where GENACCCD between 600000000 and 799999999 And MNGSOCCD = 1 AND MNGPOSCD in (1,2)
              and DOCEXECD = 2011 and DOCPERCD = 06;;
              ;
              ;;
              LEFT JOIN (COMPTA)
              LOAD *;
              SQL select ACCCODAC as COMPTE
                        ,ACCDS1AC as COMPTE_LIB,
              ////          ACCALPAC,
                        LE8001AC as COMPTE_GR1,
                        LE8002AC as COMPTE_GR2,
                        LE8003AC as COMPTE_GR3,
                        LE8004AC as COMPTE_GR4,
                        LE8005AC as COMPTE_GR5 
                  from CACCOUTB where ACCTYPAC = 'GEN' and MNGSOCAC = '001' and MNGPOSAC = '000' 
              ;;
              LEFT JOIN (COMPTA)
              LOAD * ;
              SQL select COMNUMCP as COMNUMCD
                        ,COMALPCP as CODE_FOURNISSEUR,
                          COMNA1CP as FOURNISSEUR
                  from ECOMPYTB where MNGSOCCP = '001'
              ;;
              LEFT JOIN (COMPTA)
              //LU_PRODUITS : 
              LOAD * ;
              SQL select ACCCODAC as  ANAACCCD
                       ,ACCALPAC  as PRODUIT
                  from CACCOUTB where ACCTYPAC = 'ANA' and MNGSOCAC = '001' and MNGPOSAC = '002'
              ;;    
              LEFT Join (COMPTA)
              //LU_DOSSIERS :
              LOAD * ;
              SQL SELECT ACCCODAC as FLRREFCD
                 ,ACCALPAC  as DOSSIER
                  from CACCOUTB where ACCTYPAC = 'FLD' and MNGSOCAC = '001' and MNGPOSAC = '002'
              ;;    
              //
              //
              LEFT JOIN (COMPTA)
              //LU_PROJETS : 
              LOAD * ;
              SQL  select NBRPRJPD as PURNBRCD
                    ,ACCALPPD as PROJET,DESCR1PD  
                   from EPRDETDT where MNGSOCPD = '001'and MNGPOSPD = '002' 
              ;;
              LEFT JOIN (COMPTA)
              //LU_SERVICES :
              load *; 
              SQL select CONNUMCN as SALNBRCD
                        ,CONNA1CN as SERVICE
               from ECONTATB where MNGSOCCN = '001' and MNGPOSCN = '002'  
               ;;
              
              


              Bye