8 Replies Latest reply: May 14, 2015 1:00 PM by mahathi vucha RSS

    Incremental QVD

    mahathi vucha

      Hello Guys,

       

      I have a situation in my Application: We have open orders in a table which once invoiced will be moved to the invoice table.

      but the order number is not same it is changed once it is invoiced.

       

      In my situation I am trying to create the QVD's for entire application, I wrote the Incremental QVD (Insert Only) for the invoice table,

      But I am confused which kind of Incremental QVD should I use for the open orders table?

      I 1st thought of using the insert/update/delete type but then I was confused which column I am supposed to use as a Primary Key.


      Also for the order number to use as a primary key, It is repeating, it is not set to like for only one row, because the order number may have multiple items and its each row for each item. Also i am not able to see any other column as a primary key, Since non have that functionality.

       

      Can I still use Order Number a primary key even though it is repeating?  Will it really work and insert/update/delete the records without reading the whole table.

       

      Can anyone please give me the scripts to do so?

       

      Thank you so much.

        • Re: Incremental QVD
          Ramon Covarrubias

          I would assume serial number is your lowest granular attribute, in that case you can create a composite key for the for the sale order and serial number, that will give you a unique entry to use the incremental logic


          order:

          load

          serial&'_'&saleorder as %K_Order,

          fields ....

          from your source;

           

           

          concatenate(order)

          load

          %K_Order,

          fields ....

          from you incremental qvd

          where not exists(%K_Order,%K_Order);

            • Re: Incremental QVD
              mahathi vucha

              Hi Ramon,

               

              Your script seems so helpful, very thankful for that.

               

              but the problem there no such column as serial or sequence number. But i do have item number field which when used with order number creates a kind of unique key. can i use it.

                • Re: Incremental QVD
                  Oscar Marquez

                  Insert/update/delete type is ok, you can use just Order number as primary key, it is repeating, yes, but you won't use it to join tables but just to identify the new ones and a order number won't appear again if it already was used in the past, the only problem is that you are saying it changes when it's delivered, do you get all the data in a single table with invoices and orders together?

                    • Re: Incremental QVD
                      mahathi vucha

                      Hello Oscar,

                       

                      NO the open orders table is different and invoice is diff table once order is completed they invoice it, and it moves into invoice table with its order number changed.

                       

                      but again say if we have 10 items under one order that order has 10 rows in the table but if we ship only 7 items that 7 rows are deleted from the open orders table and moved to invoice table and the remaining 3 still sits in the open orders table until they are shipped with that same order number.

                       

                       

                      also what do you mean by joining tables?