SQL View to return Listing of Effective Products

Question

Please assist with creating an SQL view.

On DB2 For i V7R2

Situation:

Departments at my company are allowed to sell a listing of products, up until they are replace with a new product. On the day that the new product becomes effective the Department is allowed to sell both Products. At the COB, the old product is no longer allowed to be sold, and needs to be returned.

Required:

SQL query to return the listing of "allowed" products for a specific date.

The query needs to return:

"Green-Ladder" and "Red-Ladder" `WHERE EFFDAT = CURRENT_DATE

Example Data Set:

drop table QTEMP/Product_EffectiveDate_TestTable;

create table  QTEMP/Product_EffectiveDate_TestTable (
    Dept    varchar(50) not null,
    EffDat  date        not null,
    PrdCde  varchar(50) not null);

insert into  QTEMP/Product_EffectiveDate_TestTable
    ( Dept, EffDat, PrdCde)
 values
    ('Department A', CURRENT_DATE + 10 DAY  , 'Blue-Ladder'),
    ('Department A', CURRENT_DATE           , 'Green-Ladder'),
    ('Department A', CURRENT_DATE - 10 DAY  , 'Red-Ladder'),
    ('Department A', CURRENT_DATE - 20 DAY  , 'Yellow-Ladder') ;

Answer 1

My answer for a single product per department is:

select * 
  from qtemp.Product_EffectiveDate_TestTable a
  where effdat = (select max(effdat) 
                  from qtemp.Product_EffectiveDate_TestTable
                  where effdat < current_date
                    and dept = a.dept)
     or effdat = current_date

You can convert this to a view if you are only interested in products for the current date. However if you want to be able to query it for any given date, you will have to create a table function.

The view would look something like this:

create view Products_By_Department as
select * 
  from qtemp.Product_EffectiveDate_TestTable a
  where effdat = (select max(effdat) 
                  from qtemp.Product_EffectiveDate_TestTable
                  where effdat < current_date
                    and dept = a.dept)
     or effdat = current_date;

The UTF could look like this:

create or replace function xxxxxx.UTF_ProductsByDepartment
  (
    p_date Date
  )
  returns table
  (
    Dept    Varchar(50),
    EffDat  Date,
    PrdCde  Varchar(50),
  )
  language sql
  reads sql data
  no external action
  not deterministic
  disallow parallel
  return
    select dept, effdat, prdcde 
      from qtemp.Product_EffectiveDate_TestTable a
      where effdat = (select max(effdat) 
                      from qtemp.Product_EffectiveDate_TestTable
                      where effdat < p_date
                        and dept = a.dept)
         or effdat = p_date;

You would use the UTF like this:

select * from table(xxxxxx.utf_ProductsByDepartment(date('2017-06-13'))) a

Note that you cannot put a function in QTEMP, so you will have to replace xxxxxx with an appropriate library, or you can leave it unqualified, and set the default schema some other way.