I am working with DB2 and its feature "Temporal logic" (if you are interesting in the topic - https://www.ibm.com/developerworks/data/library/techarticle/dm-1204db2temporaldata/index.html gives an introduction). I got one question about an issue which I cannot understand so far:
This example will work perfectly fine:
SELECT * FROM policy
FOR BUSINESS_TIME FROM '2009-01-01' TO '2011-01-01'
But if I want to join more tables, I get errors that the syntax is wrong. And there are no examples (read: I cannot find examples) of how to do it.
Yes, I know how to avoid this "feature", and make it work with sub select. And even between date_from and date_to won't help here. Because this BUSINESS_TIME is not equal date_to.
In my case, something like:
where'2009-01-01' <='2009-01-01'<'2011-01-01'
doesn't work.
Update in DB2 should be like this:
where '2009-01-01'<='2009-01-01'
and '2009-01-01'<'2011-01-01'
Thanks a lot in advance!
Joining also works - this is one of my examples joining two tables on BUSINESS TIME
SELECT u.name, u.BUSINESS_START, u.BUSINESS_END
, d.name, d.BUSINESS_START, d.BUSINESS_END
, max(u.BUSINESS_START, d.BUSINESS_START) as Result_BUSINESS_START
, min(u.BUSINESS_END, d.BUSINESS_END) as Result_BUSINESS_END
FROM Praesident_USA FOR business_time
FROM '1970-01-01' TO CURRENT DATE U
INNER JOIN KANZLER_D FOR business_time
FROM '1970-01-01' TO CURRENT DATE D
ON d.BUSINESS_START <= u.BUSINESS_END
AND d.BUSINESS_END >= u.BUSINESS_START
I hope you find this helpful for your scenario.