3 Replies Latest reply: Nov 20, 2013 4:27 AM by Fabrice Aunez RSS

    Multiple Database in a simple script

    bing bernabe

      Hi there,

       

      I have tried to use multiple database in ODBC connect to qlikview, but i don't know the exact logic to link this two database

      Here my script:

       

      ODBC CONNECT TO RAPS_DEV (XUserId is RURLDZFMQbcEE, XPassword is LOXOEZFMQbcCDZMGbF);

       

      SQL SELECT FIRST 50 A.EVALHDRID, A.ACCTMASTERID,A.KMREADING,A.PLATENO,A.MPORNO,

                 E.EVALHDRID AS "SUMRY_HDRID", E.TOTALCOSTAMT, E.SELLINGPRICE, E.CLASSIFICATION,

                 M.ASSETMAKE, M.ASSETMODEL, M.ENGINENO, M.CHASSIS, M.COLOR,B.BRANCHCODE AS "MC_BRANCH"

              FROM EVALHDR A

                 JOIN

                 (SELECT EVALHDRID, TOTALCOSTAMT, SELLINGPRICE, CLASSIFICATION

             FROM EVALPRICESUMRY)E ON E.EVALHDRID = A.EVALHDRID

                JOIN (SELECT BRANCHCODE,MCMASTERID FROM ACCTMASTER)B ON B.MCMASTERID = A.ACCTMASTERID

                JOIN (SELECT MCMASTERID, ASSETMAKE, ASSETMODEL, ENGINENO, CHASSIS, COLOR FROM MCMASTER)M ON     M.MCMASTERID = A.MCMASTERID;

            

      ODBC CONNECT TO BIMS (XUserId is UbXGLRFNNbcCD, XPassword is AJfCPRFNNbcCDZMGPG);

       

      SQL SELECT F.BRANCHCODE, F.BRANCHSNAME

      FROM BIMS.BRANCH F ON F.BRANCHCODE = B.BRANCHCODE;

       

      Please need advise. Thank you

        • Re: Multiple Database in a simple script

          Hi Bernabe,

           

          Hope the following code will help..

           

           

           

          ODBC CONNECT TO RAPS_DEV (XUserId is RURLDZFMQbcEE, XPassword is LOXOEZFMQbcCDZMGbF);

          Table1: //Load the Sql result

          Load   EVALHDRID,

            ACCTMASTERID,

            KMREADING,

            PLATENO,

            MPORNO,

            SUMRY_HDRID,

            TOTALCOSTAMT,

            SELLINGPRICE,

            CLASSIFICATION,

            ASSETMAKE,

            ASSETMODEL,

            ENGINENO,

            CHASSIS,

            COLOR,

            MC_BRANCH

           

           

          SQL SELECT FIRST 50 A.EVALHDRID, A.ACCTMASTERID,A.KMREADING,A.PLATENO,A.MPORNO,

                     E.EVALHDRID AS "SUMRY_HDRID", E.TOTALCOSTAMT, E.SELLINGPRICE, E.CLASSIFICATION,

                     M.ASSETMAKE, M.ASSETMODEL, M.ENGINENO, M.CHASSIS, M.COLOR,B.BRANCHCODE AS "MC_BRANCH"

                  FROM EVALHDR A

                     JOIN

                     (SELECT EVALHDRID, TOTALCOSTAMT, SELLINGPRICE, CLASSIFICATION

                 FROM EVALPRICESUMRY)E ON E.EVALHDRID = A.EVALHDRID

                    JOIN (SELECT BRANCHCODE,MCMASTERID FROM ACCTMASTER)B ON B.MCMASTERID = A.ACCTMASTERID

                    JOIN (SELECT MCMASTERID, ASSETMAKE, ASSETMODEL, ENGINENO, CHASSIS, COLOR FROM MCMASTER)M ON     M.MCMASTERID = A.MCMASTERID;

              

          ODBC CONNECT TO BIMS (XUserId is UbXGLRFNNbcCD, XPassword is AJfCPRFNNbcCDZMGPG);

          Table2: //Load the Sql result

          Load BRANCHCODE,

            BRANCHSNAME

           

           

          SQL SELECT F.BRANCHCODE, F.BRANCHSNAME

          FROM BIMS.BRANCH F ON F.BRANCHCODE = B.BRANCHCODE;

           

           

           

           

          //Now Join the Both Tables

           

           

          Main_Table

           

           

          Load EVALHDRID,

            ACCTMASTERID,

            KMREADING,

            PLATENO,

            MPORNO,

            SUMRY_HDRID,

            TOTALCOSTAMT,

            SELLINGPRICE,

            CLASSIFICATION,

            ASSETMAKE,

            ASSETMODEL,

            ENGINENO,

            CHASSIS,

            COLOR,

            MC_BRANCH as BRANCHCODE

          Resident Table1;

           

          Drop Table Table1;

           

           

          inner join (Table1)

           

           

          Load BRANCHCODE,

            BRANCHSNAME

          Resident Table2;

           

          Drop Table Table2;

            • Re: Multiple Database in a simple script
              bing bernabe

              Hi there,

              i appreciate you fast respond, my concern is about the two database how to link to each other?

               

              first database RAPS_DEV

              table: EVALHDR

                             field ACCTMASTERID and

                       ACCTMASTER

                             field MCMASTERID, BRANCHCODE

               

              second database BIMS

              table: BRANCH

                             field BRANCHCODE, BRANCHSNAME

               

              my problem is how can i connect the first database field to the second database field?

               

              Highly appreciated your respond

              Thanks.

                • Re: Multiple Database in a simple script

                  Hi,

                   

                  When you load data from sources (databases, files, hard coded data), this data is inside tables.

                  The tables are linked if you have fields in common: it means with the same name.

                  In other words, if you have two tables with a Product field, they will be joined. Automatically.

                   

                  If you want to load the second source into the same table as the 1st one, you must:

                  - either have the same fields names

                  - use the CONCATENATE keyword

                  - use a JOIN before the LOAD statement

                   

                  Fabrice