3 Replies Latest reply: May 2, 2012 6:12 AM by Mohammed Mukram Ali RSS

    How to perform delete through Incremental Reload

    Mohammed Mukram Ali

      Dear,

      Community.

       

       

      I am trying to perform Incremental Reload using the following method

       

      Step 1: Generating a QVD for the data from the DB.

      Step 2: Fetching the updated data (using a where condition) and concatenating this data with the stored QVD

      I am using a PKey  (eg: ItemID) and date fields (eg: Creation Date and Modified Date)

      Condition : not exists or <>

       

      Step 3: Left Joining the result set with the QVD data to delete any records that were removed from the source DB.

       

       

      The problem is I can do insert and update but delete is not being performed.

        • Re: How to perform delete through Incremental Reload
          Mohammed Mukram Ali

          Dear,

          Community Members.

           

          I need to Perform Incremetnal Reload.

           

          till now what i did , the follwing Steps,

           

          1)  I fetch the data from Sql server Database.

          2) My Database Table Contain

                             ItemID(Primary Key),

                              ItemDescription,

                              ItemPrice,

                              CreationDate,

                               ModificationDate,

                                CreatedBy

          fields.

          3) Then i created a Qvd file.(Item.qvd)

          4)  I Uncommented That Qvd file.(//)

          5) i created a new Table by Resident (Item.qvd).

          6) the new Table contains Max(CreationDate) as MaxCreationDate,

                                                 Max(ModificationDate) as MaxModificationDate.

          7) then I stored those two fileds into Variables by using Peek Function.

          7) then i loaded the table again from DB by applying the following  Where Condition

           

                              where ModificationDate>'$(MaxModificationDate)' or CreationDate>'$(MaxCreationDate)

          8) The Result table i Concatenate with Item.qvd file by applying where condition (where NOT EXISTS(ItemID);

          9) Then I stored The Result Table into the Item.QVD;

          10) by the conditions what i applied i', able to get New Records As well as Updated Records  From the Database.

           

          BUT when i delete a Record From Database Table it's Not going to be deleted from My QVD file.

           

           

          So I need When ever AnyRecord is Delted from DB Table It should by Reflected in my QVW application .

           

          So please Somebody Can help me Out.

           

          Thanks,
          Mukram.

            • Re: How to perform delete through Incremental Reload
              Marc Livingston

              Use a inner join from the database table using a key field. Usually the key field is indexed so it performs fast.

               

              The inner join will remove all rows from the QVD if they do no exist in the database table.

               

              Let ThisExecTime= (today());

              Order1:

               

              SQL SELECT

                  company + cast(ordernum as varchar) as primarykeyorder,

                  company,

                  custnum,

                  orderdate,

                  shpconnum,

                  salesreplist,

                  openorder,

                  orderamt,

                  changedate,

                  ponum,

                  shiptonum,

                  ordernum,

                  requestdate

              FROM epicor904.dbo.orderhed

              where ((changedate) >= $(LastExecTime) AND (changedate) < $(ThisExecTime))

              ;

               

              Concatenate LOAD

                  company,

                  primarykeyorder,

                  custnum,

                  orderdate,

                  shpconnum,

                  salesreplist,

                  openorder,

                  orderamt,

                  changedate,

                  ponum,

                  shiptonum,

                  ordernum,

                  requestdate FROM ... (qvd)

              WHERE NOT EXISTS(primarykeyorder);

               

              Inner Join SQL SELECT company,ordernum FROM epicor904.dbo.orderhed;

               

              If ScriptErrorCount = 0 then

              STORE Order1 into.....;

              Let LastExecTime =  ThisExecTime;                                           

               

              End If