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