14 Replies Latest reply: Jun 19, 2017 10:33 AM by Ronald van der Does RSS

    Issue with Loading data from two databases

    Saimeenakshi Sankar

      Hi all,

       

      I am trying to load data from two different databases with same table structure and column names.

      My script as follows:

      ODBC CONNECT TO [PT1025;DBQ=PT1025.IKEADT.COM] (XUserId is afMJeBdJTSdA, XPassword is ZBJfUBdJTaNOTbYW);
      EU:
      SQL SELECT CUSORD_NO,CUSORD_STAT,CUSORD_DATE,CUST_NO,CUS_NAME,CTY_CODE,USER_ID_REF
      FROM COS."CUSTOMER_ORDER" WHERE CUSORD_STAT='00' AND TO_DATE(TO_CHAR(CUSORD_DATE,'MM/DD/YYYY'),'MM/DD/YYYY')>= TO_DATE('$(vToday)','MM/DD/YYYY');

       

      ODBC CONNECT TO [PT1028;DBQ=PT1028.IKEADT.COM] (XUserId is VQZJaBdJTCBB, XPassword is efCcYBdJTaNOTbEE);
      EUX:
      SQL SELECT CUSORD_NO,CUSORD_STAT,CUSORD_DATE,CUST_NO,CUS_NAME,CTY_CODE,USER_ID_REF
      FROM COS."CUSTOMER_ORDER" WHERE CUSORD_STAT='00' AND TO_DATE(TO_CHAR(CUSORD_DATE,'MM/DD/YYYY'),'MM/DD/YYYY')>= TO_DATE('$(vToday)','MM/DD/YYYY');

       

      However, I could see that after reload only the first table got loaded.  I have no idea why the second table is not loading. Any thoughts ?

       

       

        • Re: Issue with Loading data from two databases
          Antonio Mancini

          Hi,

          because You have same Name Fields in both table, then QV Concatenate

          • Re: Issue with Loading data from two databases
            Sunny Talwar

            Try this

             

            ODBC CONNECT TO [PT1025;DBQ=PT1025.IKEADT.COM] (XUserId is afMJeBdJTSdA, XPassword is ZBJfUBdJTaNOTbYW);
            EU:
            SQL SELECT CUSORD_NO,CUSORD_STAT,CUSORD_DATE,CUST_NO,CUS_NAME,CTY_CODE,USER_ID_REF
            FROM COS."CUSTOMER_ORDER" WHERE CUSORD_STAT='00' AND TO_DATE(TO_CHAR(CUSORD_DATE,'MM/DD/YYYY'),'MM/DD/YYYY')>= TO_DATE('$(vToday)','MM/DD/YYYY');

             

            ODBC CONNECT TO [PT1028;DBQ=PT1028.IKEADT.COM] (XUserId is VQZJaBdJTCBB, XPassword is efCcYBdJTaNOTbEE);
            EUX:

            NoConcatenate
            SQL SELECT CUSORD_NO,CUSORD_STAT,CUSORD_DATE,CUST_NO,CUS_NAME,CTY_CODE,USER_ID_REF
            FROM COS."CUSTOMER_ORDER" WHERE CUSORD_STAT='00' AND TO_DATE(TO_CHAR(CUSORD_DATE,'MM/DD/YYYY'),'MM/DD/YYYY')>= TO_DATE('$(vToday)','MM/DD/YYYY');

            • Re: Issue with Loading data from two databases
              Ronald van der Does

              stalwar1 is right, you could use NoConcatenate

               

              However, there are good reasons for concatenating these two tables. Without your full requirements it's hard to say which solutions works best from an architectural point of view.

               

              The other option is adding a " Source"-field to your table.

               

              ODBC CONNECT TO [PT1025;DBQ=PT1025.IKEADT.COM] (XUserId is afMJeBdJTSdA, XPassword is ZBJfUBdJTaNOTbYW);
              TableName:
              LOAD
              'EU' AS Source,
              *;
              SQL SELECT CUSORD_NO,CUSORD_STAT,CUSORD_DATE,CUST_NO,CUS_NAME,CTY_CODE,USER_ID_REF
              FROM COS."CUSTOMER_ORDER" WHERE CUSORD_STAT='00' AND TO_DATE(TO_CHAR(CUSORD_DATE,'MM/DD/YYYY'),'MM/DD/YYYY')>= TO_DATE('$(vToday)','MM/DD/YYYY');
              
              ODBC CONNECT TO [PT1028;DBQ=PT1028.IKEADT.COM] (XUserId is VQZJaBdJTCBB, XPassword is efCcYBdJTaNOTbEE);
              Concatenate (TableName):
              LOAD
              'EUX' AS Source,
              *;
              SQL SELECT CUSORD_NO,CUSORD_STAT,CUSORD_DATE,CUST_NO,CUS_NAME,CTY_CODE,USER_ID_REF
              FROM COS."CUSTOMER_ORDER" WHERE CUSORD_STAT='00' AND TO_DATE(TO_CHAR(CUSORD_DATE,'MM/DD/YYYY'),'MM/DD/YYYY')>= TO_DATE('$(vToday)','MM/DD/YYYY');
              
              • Re: Issue with Loading data from two databases
                Antonio Mancini

                I don't recommend You to use NoConcatenate, because after reload You will have many synthetic Keys.

                Without ,You have Table 'Union' of Two tables.  Why this is problem ?

                  • Re: Issue with Loading data from two databases
                    Antonio Mancini

                    Another way to have two Distinct Tables is Qualify instruction.

                    e.g.

                    Table:

                    LOAD (or Select) A,B,C,D from File1;

                    Qualify *;

                    Table1:

                    LOAD (or Select) A,B,C,D from File2;

                    UnQualify *;

                    Table1 will have Names Field like Table1.A,Table2.B,......

                      • Re: Issue with Loading data from two databases
                        Friedrich Hofmann

                        Hi,

                         

                        you could also load the first table, then write it to a qvd, delete it, then load the second table - and then load the first one again from the qvd (much faster this time) with a >> concatenate << - if you do want to concatenate the tables.

                        Otherwise, using the QUALIFY statement as Antonio suggests would work well, making all the field_names different between the tables.

                        Any way, it's hard to tell you how to do what you want to do without knowing what it is that you want to do ;-)

                        Best regards,

                         

                        DataNibbler

                    • Re: Issue with Loading data from two databases
                      Saimeenakshi Sankar

                      Dear All, my requirement is loading both the table (who has exactly same structure) from two diff. DB`s. Need to create a Dashboard with count () orders from the tables (separately and together) that's one of the KPIs goes into the Dashboard. similarly I need to create other KPI`s together and separately. How can I achieve this?

                      Concatenate? Join? or Union?

                       

                      As mentioned by Antonio NoConcatenate explicitly loads however I get synthetics.