user8444 user8444 - 14 days ago 7
SQL Question

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

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

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:

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:

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)

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.



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.


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)

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

                CMD(RUNSQL SQL('UPDATE cur_dat SET cur_dat = CURRENT DATE') COMMIT(*NONE))

Lastly change your MQT definition to this:

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