Incremental Load problem

[#1]

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)))
;

[rwunderlich]

What's the format of LastExecTime?

-Rob

[#3]

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.

[#4]

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  )

[#5]

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.