9 Replies Latest reply: Jun 11, 2012 10:07 AM by hannesse RSS

    Incremental load - scripting problems

      Hi!

       

      I have som problem writing my script that should be used for incremental load of data. As always when using incremental load I just want to load the posts that is new by looking at the field called CREATE_TIMESTAMP.

      The problem is that i have to tables that i want to perform incremental load on, lets call it table1 and table2, in an Oracle database. It works fine when i am loading only from table1, only the new posts is loaded in to my qvd-file each time. When this is done i just want to load the data in table2 that is connected to the loaded posts in table1 since the other data already should be loaded. Here's the catch, the CREATE_TIMESTAMP is only in table1 and table2 is associated with table1 with an key (table1.ID = table2.ID2). I ONLY want to load the posts from table2 that is associated via the key on the loaded posts in table1.

       

      Hope you understand my problem.

       

      Thank you.

      Hannes

        • Incremental load - scripting problems
          Shantanu Sardar

          Hello Hannesse,

           

          I am not so very clear. If u provide me the script I may understand. And then I may provide you the solution.

           

          Thank

           

          Shantanu

            • Incremental load - scripting problems

              shantanu73, heres the script:

               

              /* Setup for incremental load. */

              SET vQvdFile='File.qvd';

              SET vPKID='ID1';

               

              // Set a variable indicating if the QVD exists or not. -1 is True, 0 is False

              LET vQvdExists = if(FileSize('$(vQvdFile)') > 0, -1, 0);

               

              IF $(vQvdExists) THEN                    // QVD exists, we will do an incremental reload

                        maxdateTab:

                        // Get the max date from this QVD for use in incremental SELECT

                        LOAD max(CREATE_TIMESTAMP) as maxdate

                        FROM $(vQvdFile) (qvd);

                LET vIncrementalExpression = 'WHERE CREATE_TIMESTAMP >= ' & chr(39) & timestamp(peek('maxdate')) & chr(39);

                        DROP table maxdateTab;

               

              ELSE                                                            // QVD does not exist

                        LET vIncrementalExpression = '';           // No QVD. Force full reload

              END IF

               

              ODBC CONNECT TO [database;DBQ=DATABASE ] (XUserId is XXXXX, XPassword is YYYY);

               

               

              Table1:

              SQL SELECT BIRTHDATE,

                  "FIRST_NAME",

                  GENDER,

                  "CREATE_TIMESTAMP",

                  ID as "ID1",                                        //Primary Key

                  "LAST_NAME",

                  "WORK_TITLE",

              FROM PROD.TABLE1; 

               

              //Heres the problem, i only want to load the posts that is associated with the newly loaded posts from table1

              //Maybe i need to store old posts from table2 in another qvd-file as well and concatenate the new posts to that?

              Table2:

              SQL SELECT "DATE",

                  "RESULT",

                  ID2,

                  ID1 as "Table2_ID"    //NOTE THAT ID2 is the field that should be associated with ID1 in table1

              FROM PROD.TABLE2;

               

               

              /*

              Update the QVD with changes.

              */

               

              Directory;

              IF $(vQvdExists) THEN

                        // Use CONCATENATE in case we've added any new fields.

                        CONCATENATE (Table1) LOAD * FROM $(vQvdFile) (qvd)

                        WHERE NOT exists($(vPKID))          // Load only QVD rows that were not already loaded in the data load.

                        ;

              END IF

               

              //Overwrite the QVD with the QV datatable. 

               

              STORE Table1 INTO $(vQvdFile);

               

               

               

               

               

              THANKS!

              /Hannes

                • Incremental load - scripting problems
                  Shantanu Sardar

                  Hello Hannes,

                   

                  Sorry of Late Answer,

                   

                  Check the following Script. If you get some idea.

                   

                  //Start from Here:

                   

                  Let ThisExecTime = ReloadTime();

                  QV_Table:
                  SQL SELECT PrimaryKey, X, Y FROM DB_TABLE
                  WHERE ModificationTime >= #$(LastExecTime)#
                         AND ModificationTime < #$(ThisExecTime)#;

                  Concatenate LOAD PrimaryKey, X, Y FROM File.QVD
                  WHERE NOT EXISTS(PrimaryKey);

                  Inner Join SQL SELECT PrimaryKey FROM DB_TABLE;

                  If ScriptErrorCount = 0 then
                  STORE QV_Table INTO File.QVD;
                  Let LastExecTime = ThisExecTime;
                  End If

                  //End from Here:

                   

                  Regards

                   

                  Shantanu

              • Incremental load - scripting problems

                You Chek this One.

                 


                Table_Name:

                                              LOAD *;

                                              SQL  SELECT  * FROM Table_Name

                                                        where TO_CHAR(to_DATE($(vModificationTime),'YYYY/MM/DD HH24:MI:SS'),'YYYYMMDDHH24MISS')>='$(T)';

                 

                                              Concatenate ([Table_Name])

                 

                 

                Table_Name:

                                              LOAD * from Table_Name .qvd (qvd)

                                              WHERE NOT Exists ($(Y));

                 

                                              inner join ([Table_Name])

                 

                 

                                              sql select Pk  FROM Table_Name;

                          ENDIF

                 

                                    store Table_Name into Table_Name.qvd ;

                 

                //..............................................................................................//

                 

                                    LET T= (DATE(NOW(),'YYYYMMDD')  & ((hour(NOW()))*10000)  + (MINUTE(NOW())*100)  + SECOND(NOW()));