iSeries SQL: current date not allowed when selecting records to create a table

Question

I need to create a materialized query table with records which have an attribute retrieved with a sub query on a second table, in order to get the most recent value of that attribute. This question is very similar to the one discussed here:Retrieving the last record in each group except the following:

• inner sub query must compares second table's validity date to current_date (or CURDATE) not to another date field from primary table
• results are used to create a new materialized query table

Under these assumptions the SQL ends abnormally giving SQ20058 error, which can be eliminated in two ways:

• using a specific date instead of current_date, which makes the statement useless to my purposes

• adding

  disable query optimization
  

  to current settings at the end of the statement

  data initially deferred 
  refresh deferred        
  maintained by user
  

  which in turn disables query optimization made by iSeries V7R1 operating system.

To solve the problem, I tried to create a view with all records extracted by inner sub query, then issue a create table statement joining the view, so that to avoid sub query execution, but again materialized query table can't address a view which contains references to current date.

Do you know how can I compare records to current date when creating tables? What am I missing?

Thank you

Example: 1) given sales table defined as:

create table sales (
item_id decimal(3, 0),
sale_date date,         
sale_qty decimal(7, 2)
)

with the following content:

ITEM_ID  SALE_DATE   SALE_QTY 
    1    2016-01-10     10,00 
    1    2016-02-10     10,00
    1    2016-03-10     10,00 
    2    2016-01-10      5,00 
    2    2016-02-10      5,00 
    2    2016-03-10      5,00 

2) and depts table defined as:

create table depts (
item_id decimal(3, 0),
dept_from_date date,         
dept_id character(3)
)

with the following content:

ITEM_ID  DEPT_FROM_DATE  DEPT_ID
    1       2016-01-01     AAA  
    1       2016-03-01     BBB  
    2       2016-01-01     BBB  
    2       2016-02-01     CCC  
    2       2016-05-01     DDD  
    2       2016-12-01     EEE  

3) that is the expected result:

select s.item_id, s.sale_date, s.sale_qty, d.dept_id
from sales s
left join depts d on d.item_id=s.item_id
and d.dept_from_date = (
select max(dept_from_date)
from depts x
where x.item_id = d.item_id and dept_from_date <= current_date)

  ITEM_ID  SALE_DATE   SALE_QTY   DEPT_ID
    1    2016-01-10     10,00     BBB  
    1    2016-02-10     10,00     BBB  
    1    2016-03-10     10,00     BBB  
    2    2016-01-10      5,00     DDD  
    2    2016-02-10      5,00     DDD  
    2    2016-03-10      5,00     DDD  

where department id is flattened to its most recent value for BI purposes

4) when previous select statement is embedded in a create table statement like this:

create table sales2 as (
select s.item_id, s.sale_date, s.sale_qty, d.dept_id
from sales s
left join depts d on d.item_id=s.item_id
and d.dept_from_date = (
select max(dept_from_date)
from depts x
where x.item_id = d.item_id and dept_from_date <= current_date))
data initially deferred
refresh deferred       
maintained by user

it returns SQ20058 error.

Answer 1

Edit

You can just add the "DISABLE QUERY OPTIMIZATION" parameter to the end of your MQT. From the documentation all that it does is:

Specifies that the materialized query table cannot be used for query optimization. The table can still be queried directly.

iSeries DB2 CREATE TABLE

So unless you want the system to maintain the MQT and the query optimizer to use the MQT for select statements on the original tables you can just specify the DISABLE QUERY OPTIMIZATION parameter.

Original Answer

I am leaving this here in case of "data initially deferred refresh immediate" parameters. Specified for system maintained MQTs.

Seeing that MQTs without the "DISABLE QUERY OPTIMIZATION" parameter does not allow you to use special registers:

Subselect for materialized query table SALES2 not valid for reason code 8.

8 -- Refers to a special register or global variable.

There isn't any proper way of using CURRENT_DATE in the MQT's definition. So I would personally suggest that you rather just create a view based on your select statement.

That being said, here is a hack (or more like a chainsaw masacre) method that you could do:

Create a table that will store the current date:

CREATE TABLE cur_dat (cur_dat DATE)
INSERT INTO cur_dat VALUES(CURRENT DATE)

Then create a Job Schedule Entry that will update this table at midnight every night:

QSYS/ADDJOBSCDE JOB(UPD_CURDAT)
                CMD(RUNSQL SQL('UPDATE cur_dat SET cur_dat = CURRENT DATE') COMMIT(*NONE))
                FRQ(*WEEKLY)
                SCDDATE(*NONE)
                SCDDAY(*ALL)
                SCDTIME('00:00:00')

Lastly change your MQT definition to this:

CREATE TABLE sales2 AS (
SELECT s.item_id, s.sale_date, s.sale_qty, d.dept_id
FROM sales s
LEFT JOIN depts d ON d.item_id = s.item_id
  AND d.dept_from_date = (SELECT MAX(x.dept_from_date)
    FROM depts x, cur_dat y
    WHERE x.item_id = d.item_id
      AND x.dept_from_date <= y.cur_dat)
)
data initially deferred refresh immediate