4 Replies Latest reply: Aug 29, 2012 8:32 AM by Marc Livingston RSS

    Incremental Load problem

      I have been struggling with getting incremental load to work with SQL 2005. Our ERP software stores the changedate and then the changetime in seconds since midnight. I used CAST to convery this into a single decimal field that I can compare with Qlikview.

       

      No matter what I try I have not been able to get this to work. Can anyone see anything wrong here?

       

      WHERE 
      (
          (
          CAST((CAST(changedate AS decimal(14)) +  (CAST(changetime AS decimal(14)) /CAST(86400 AS decimal(14))))AS DECIMAL (14))
          < ($(ThisExecTime)))
          AND
          (
          CAST((CAST(changedate AS decimal(14)) +  (CAST(changetime AS decimal(14)) /CAST(86400 AS decimal(14))))AS DECIMAL (14))
           >= ($(LastExecTime))) 
      )
      ;
      

       

       

      Note: Just doing the followng works fine, it seems to have something to do with the second >= part.

       

      WHERE 
      (CAST((CAST(changedate AS decimal(14)) +  (CAST(changetime AS decimal(14)) /CAST(86400 AS decimal(14))))AS DECIMAL (14))
          < ($(ThisExecTime)))
      ;
      
        • Re: Incremental Load problem
          Rob Wunderlich

          What's the format of LastExecTime?

           

          -Rob

            • Re: Incremental Load problem

              Let ThisExecTime= num(now());

               

               

               

              Let LastExecTime = ThisExecTime;

               

              After it runs and does not return the wanted records I just go in a lower the amount in the variable by a day.

              • Re: Incremental Load problem

                Full Script that I have been trying to use. Ran yesterday morning and had it set everything/load everything to the QVD. Ran it again today and nothing new came through. And Yes There are new results that are excluded for some reason.

                 

                If i remove

                AND

                    (

                    CAST((CAST(changedate AS decimal(14)) +  (CAST(changetime AS decimal(14)) /CAST(86400 AS decimal(14))))AS DECIMAL (14))

                     >= ($(LastExecTime)))

                )

                From the where clause it pulls in the correct updated/new records. This is the same format as the first portion and I have no idea why this is not working.

                 

                Let ThisExecTime= num(now());
                
                
                SELECT
                company + '-' + cast(ordernum as varchar) as primarykeyorderrel, 
                    *
                FROM orderhed
                WHERE 
                (
                    (
                    CAST((CAST(changedate AS decimal(14)) +  (CAST(changetime AS decimal(14)) /CAST(86400 AS decimal(14))))AS DECIMAL (14))
                    < ($(ThisExecTime)))
                    AND
                    (
                    CAST((CAST(changedate AS decimal(14)) +  (CAST(changetime AS decimal(14)) /CAST(86400 AS decimal(14))))AS DECIMAL (14))
                     >= ($(LastExecTime))) 
                )
                ;
                
                Concatenate LOAD 
                * FROM [...\Test.qvd(qvd)] (qvd)
                WHERE NOT EXISTS(primarykeyorderrel);
                
                Inner Join SELECT company, ordernum FROM orderhed;
                
                If ScriptErrorCount = 0 then
                STORE orderhed into [...\Test.qvd(qvd)];
                Let LastExecTime =  num(ThisExecTime);
                

                 

                 

                 

                Edit: Here is the log for the SQL Where Clause

                 

                8/21/2012 9:41:29 AM: 0023  WHERE

                8/21/2012 9:41:29 AM: 0024  (

                8/21/2012 9:41:29 AM: 0025      (

                8/21/2012 9:41:29 AM: 0026      CAST((CAST(changedate AS decimal(14)) +  (CAST(changetime AS decimal(14)) /CAST(86400 AS decimal(14))))AS DECIMAL (14))

                8/21/2012 9:41:29 AM: 0027      < (41142.40380787))

                8/21/2012 9:41:29 AM: 0028      AND

                8/21/2012 9:41:29 AM: 0029      (

                8/21/2012 9:41:29 AM: 0030      CAST((CAST(changedate AS decimal(14)) +  (CAST(changetime AS decimal(14)) /CAST(86400 AS decimal(14))))AS DECIMAL (14))

                8/21/2012 9:41:29 AM: 0031       >= (41141.445451389))

                8/21/2012 9:41:29 AM: 0032  )

                  • Re: Incremental Load problem

                    I might have found my issue, I however need to wait for sales to get in and update some orders before I can test it. I believe it was with the Cast function where I was using decimal(14). I needed to tell it to use precision 16, with 11 decimals, so I changed it to DECIMAL(16,11)

                     

                    Edit: I Updated an order to see if it works, and of course it does not...Back to square one.