Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)))
;
What's the format of LastExecTime?
-Rob
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.
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 )
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.