How to join with / subselect in a system-period temporal table

Question

I have a db2 database and a system-period temporal table, so it's easy to get the data that was valid on a specific date by

SELECT mtt_sometimes_updated_value FROM my_temp_table FOR SYSTEM_TIME AS OF TIMESTAMP('2018-01-31') WHERE mtt_key = '...'

This will give me one row (as I filter by key), but I also could add FETCH FIRST 1 ROW ONLY in case I wouldn't have the key in the where condition.

Now I want to use this as a subselect or join with another_table which has the value of mtt_key in column at_key and an at_additional_key, and an at_date_column. What I want is to combine with the my_temp_table to get every row in another_table enhanced by the mtt_sometimes_updated_value.

When I try what seems to be intuitive

SELECT at_key, at_additional_key, at_date_column,
  (SELECT mtt_sometimes_updated_value 
   FROM my_temp_table FOR SYSTEM_TIME AS OF at_date_column 
   WHERE mtt_key = at_key)
FROM another_table

I get an error INVALID PERIOD SPECIFICATION OR PERIOD CLAUSE FOR PERIOD SYSTEM_TIME. REASON CODE = 03. SQLCODE=-20524 - I can find the the error description on https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/codes/src/tpc/n20524.html - but I don't really understand it - it seems that it's not possible to use a column of another table FOR SYSTEM_TIME AS OF.

So, can I join somehow with these temporal tables or how do I get the wanted info out of those two tables? Any ideas? Any hints? Thanks.

The only way I see is the complicated way to manually try to deal with the timestamps generated by the system-period temporal table, but I hope that there is an easier way somewhere ... or just a misunderstanding of the syntax in my subselect

Here's some sample data to clarify what I have and what I want:

The table my_temporal_table has one current row which was updated on 2018-01-07

mt_key | mtt_so_up_val | (was updated on)
-------+---------------+-----------------
1      | Z             | (2018-01-07)

former updates were on

1      | Y             | (2018-01-02)
1      | X             | (2017-12-24)

The table another_table looks like this

at_key | at_additional_key | at_date_column
-------+-------------------+---------------
1      | A                 | 2018-01-01
1      | B                 | 2018-01-02
1      | C                 | 2018-01-03
1      | D                 | 2018-01-04
1      | E                 | 2018-01-05
1      | F                 | 2018-01-06
1      | G                 | 2018-01-07
1      | H                 | 2018-01-08
1      | I                 | 2018-01-09

There may be more data for different keys, but if I filter for key = 1 then the result should be

at_key | at_additional_key | at_date_column | mtt_so_up_val
-------+-------------------+----------------+--------------
1      | A                 | 2018-01-01     | X
1      | B                 | 2018-01-02     | Y
1      | C                 | 2018-01-03     | Y
1      | D                 | 2018-01-04     | Y
1      | E                 | 2018-01-05     | Y
1      | F                 | 2018-01-06     | Y
1      | G                 | 2018-01-07     | Z
1      | H                 | 2018-01-08     | Z
1      | I                 | 2018-01-09     | Z

So I want to enhance the table with the values that were valid on that specific date. Hope that's clear enough ...

Answer 1

You are rigth using a column is currently not supported - in this sytax you have to specify the date or timestamp explicitly. To lift this restriction I have opened a RFE (Request for Enhancement) - ID 111742 It is already an uncommitted candidate but vote for it if you support my request.

You could still solve it the traditional way (without the new syntax). I highly recommend this blog article "Fun with Date Ranges" for details