用于返回有效产品列表的 SQL 视图

请协助创建SQL视图。

On DB2 for i V7R2

情况：

我公司的部门可以销售产品清单，直到它们被新产品取代。在新产品生效之日，该部门被允许销售这两种产品。在COB，旧产品不再允许出售，需要退货。

必填：

SQL 查询，用于返回特定日期的"允许"产品列表。

查询需要返回：

绿色梯子"和"红色梯子" "哪里 EFFDAT = CURRENT_DATE

示例数据集：

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') ;

我对每个部门单个产品的回答是：

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

如果您只对当前日期的产品感兴趣，则可以将其转换为视图。但是，如果您希望能够查询任何给定日期，则必须创建一个表函数。

视图如下所示：

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;

UTF 可能如下所示：

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;

您可以像这样使用 UTF：

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

请注意，您不能将函数放在 QTEMP 中，因此您必须将xxxxxx替换为适当的库，或者您可以将其保留为非限定函数，并以其他方式设置默认架构。

如果

可能的话，我会通过更改您的数据设计来解决这个问题。 最好在每一行上都有一个开始和结束日期。原因：

它使查询更简单。
这是一个更清晰、更容易理解的设计。
它更加灵活，允许将来更改您的业务需求。"嘿，实际上我们仍然需要销售这个旧版本的产品"是一种有害的要求，以后会弹出，理想情况下，您将能够在不重写应用程序代码的情况下处理这个问题。

如果您无法更改数据设计，我将使用子查询来创建结束日期：

with start_end_dates as (
    select Dept,
           EffDat as start_date,
           lead (EffDat) over (partition by Dept order by EffDat) as   end_date,
           ProdCd
       from table
)
select * from start_end_dates where
    current date between start_date and coalesce(end_date,'9999-12-31');

这假定生效日期是指特定部门内的行。如果不是真的，请根据需要更改分区子句。

相关内容

最新更新

热门标签：