title: How to join with / subselect in a system-period temporal table
body: <p>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</p>
  
  <pre><code>SELECT mtt_sometimes_updated_value FROM my_temp_table FOR SYSTEM_TIME AS OF TIMESTAMP('2018-01-31') WHERE mtt_key = '...'
  </code></pre>
  
  <p>This will give me one row (as I filter by key), but I also could add <code>FETCH FIRST 1 ROW ONLY</code> in case I wouldn't have the key in the where condition.</p>
  
  <p>Now I want to use this as a subselect or join with <code>another_table</code> which has the value of <code>mtt_key</code> in column <code>at_key</code> and an <code>at_additional_key</code>, and an <code>at_date_column</code>. What I want is to combine with the <code>my_temp_table</code> to get every row in <code>another_table</code> enhanced by the <code>mtt_sometimes_updated_value</code>.</p>
  
  <p>When I try what seems to be intuitive</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I get an error <code>INVALID PERIOD SPECIFICATION OR PERIOD CLAUSE FOR PERIOD SYSTEM_TIME. REASON CODE = 03. SQLCODE=-20524</code> - I can find the the error description on <a href="https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/codes/src/tpc/n20524.html" rel="nofollow noreferrer">https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/codes/src/tpc/n20524.html</a> - but I don't really understand it - it seems that it's not possible to use a column of another table <code>FOR SYSTEM_TIME AS OF</code>.</p>
  
  <p>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.</p>
  
  <p>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</p>
  
  <p>Here's some sample data to clarify what I have and what I want:</p>
  
  <p>The table <code>my_temporal_table</code> has one current row which was updated on 2018-01-07</p>
  
  <pre>
  mt_key | mtt_so_up_val | (was updated on)
  -------+---------------+-----------------
  1      | Z             | (2018-01-07)
  </pre>
  
  <p>former updates were on </p>
  
  <pre>
  1      | Y             | (2018-01-02)
  1      | X             | (2017-12-24)
  </pre>
  
  <p>The table <code>another_table</code> looks like this</p>
  
  <pre>
  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
  </pre>
  
  <p>There may be more data for different keys, but if I filter for key = 1 then the result should be</p>
  
  <pre>
  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
  </pre>
  
  <p>So I want to enhance the table with the values that were valid on that specific date. Hope that's clear enough ...</p>