在多个日期上运行OracleSQL查询

Question

在Crystal Reports中，我使用以下查询（针对Oracle数据库）为报表中的单个字段生成数据：

SELECT SUM(e1.ENT_LOCAL_AMOUNT+e1.ENT_DISCRETIONARY_AMOUNT) AS "Entitlement"
FROM CLAIM_PERIODS cp1
JOIN ENTITLEMENTS e1
  ON cp1.CPE_REFNO=e1.ENT_CPE_REFNO
WHERE e1.ENT_REFNO=(SELECT MAX(to_number(e2.ENT_REFNO)) 
  FROM ENTITLEMENTS e2
  WHERE e1.ENT_CPE_REFNO=e2.ENT_CPE_REFNO
  AND (e2.ENT_START_DATE <= {?HB_As_At_Date} 
  AND e2.ENT_END_DATE > {?HB_As_At_Date})
  AND e2.ENT_CREATED_DATE<={?HB_As_At_Date})
  AND cp1.CPE_CPA_CPY_CODE='HB'

这可以很好地工作，并基于提供的{HB_As_At_Date}（the{？}语法是Crystal将参数值嵌入SQL（SQL）的方法。不过，上述查询的内容不是我的问题——我想做的是在几个不同的日期重复运行它，并将输出输入Crystal以供在报告中使用。

假设我希望在9月的每个星期一运行这个查询，我目前会用参数07/09/2015运行Crystal report一次，然后再运行14/09/2015，以此类推。

相反，我想将我的SELECT语句与一个查询结合使用，该查询根据需要将此列表化——每个日期都需要运行一次以上语句。输出类似于：

Date        Entitlement
07/09/2015  450,000.00
14/09/2015  460,123.00
21/09/2015  465,456.00
28/09/2015  468,789.00

有人能给我指出正确的方向吗？我应该在这里阅读哪些关键词？我认为生成一组日期并使用它们作为子查询运行SQL是非常简单的，但我不确定从哪里开始。

Answer 1

事实上，有一个更优雅的方法来解决这个问题。让我们假设主查询是t1，要使用的参数在表t2中可用。

下面是一个示例，其中子查询t1和t2可以被实表替换。

select t1.title , t2.ref_date, t2.dt_label
from (
    select 'abc' title from dual union all
    select 'def' title from dual
    ) t1
cross join
(
    select to_date('01.07.2019', 'dd.mm.yyyy') ref_date,'S1/2019' dt_label  from dual union all
    select to_date('01.12.2019', 'dd.mm.yyyy') ref_date,'Y/2019' dt_label  from dual union all
    select to_date('01.07.2020', 'dd.mm.yyyy') ref_date,'S1/2020' dt_label  from dual union all
    select to_date('01.12.2020','dd.mm.yyyy') ref_date, 'Y/2020' dt_label from dual
) t2
where t2.ref_date < to_date('01.08.2020','dd.mm.yyyy')
order by t2.ref_date, t1.title;

这样，查询保持不变，而提取参数则填充在一个辅助表中。

Answer 2

编辑参数以将输入作为多个值，并将查询更改为

使用开始或结束，但不能同时使用两者

SELECT SUM(e1.ENT_LOCAL_AMOUNT+e1.ENT_DISCRETIONARY_AMOUNT) AS "Entitlement"
FROM CLAIM_PERIODS cp1
JOIN ENTITLEMENTS e1
  ON cp1.CPE_REFNO=e1.ENT_CPE_REFNO
WHERE e1.ENT_REFNO=(SELECT MAX(to_number(e2.ENT_REFNO)) 
  FROM ENTITLEMENTS e2
  WHERE e1.ENT_CPE_REFNO=e2.ENT_CPE_REFNO
  AND e2.ENT_END_DATE in ( {?HB_As_At_Date})
  AND e2.ENT_CREATED_DATE in ({?HB_As_At_Date})
  AND cp1.CPE_CPA_CPY_CODE='HB'

Answer 3

在不使用存储过程的情况下，我能想到的唯一方法是重复（即复制/粘贴）每个日期参数的查询，然后使用UNION将它们组合为子查询。比如：

SELECT SUM(e1.ENT_LOCAL_AMOUNT+e1.ENT_DISCRETIONARY_AMOUNT) AS "Entitlement"
FROM CLAIM_PERIODS cp1
JOIN ENTITLEMENTS e1
  ON cp1.CPE_REFNO=e1.ENT_CPE_REFNO
WHERE e1.ENT_REFNO=(SELECT MAX(to_number(e2.ENT_REFNO)) 
  FROM ENTITLEMENTS e2
  WHERE e1.ENT_CPE_REFNO=e2.ENT_CPE_REFNO
  AND (e2.ENT_START_DATE <= {?HB_As_At_Date_1} 
  AND e2.ENT_END_DATE > {?HB_As_At_Date_1})
  AND e2.ENT_CREATED_DATE<={?HB_As_At_Date_1})
  AND cp1.CPE_CPA_CPY_CODE='HB'

UNION

SELECT SUM(e1.ENT_LOCAL_AMOUNT+e1.ENT_DISCRETIONARY_AMOUNT) AS "Entitlement"
FROM CLAIM_PERIODS cp1
JOIN ENTITLEMENTS e1
  ON cp1.CPE_REFNO=e1.ENT_CPE_REFNO
WHERE e1.ENT_REFNO=(SELECT MAX(to_number(e2.ENT_REFNO)) 
  FROM ENTITLEMENTS e2
  WHERE e1.ENT_CPE_REFNO=e2.ENT_CPE_REFNO
  AND (e2.ENT_START_DATE <= {?HB_As_At_Date_2} 
  AND e2.ENT_END_DATE > {?HB_As_At_Date_2})
  AND e2.ENT_CREATED_DATE<={?HB_As_At_Date_2})
  AND cp1.CPE_CPA_CPY_CODE='HB'

至于你关于为此写剧本的评论，我不知道你是如何运行你的报告的。但是，如果你有一个应用/网站运行它，那么你可以用应用/网站的语言生成SQL，并在运行它之前将其分配给报表对象。或者更好的是，您可以生成SQL，运行它，并将结果分配给报表对象。我一直这样做，因为我更喜欢我的代码运行查询，而不是报告本身，因为我在我的应用程序中遵循分层设计模式。报表将位于无法与数据库直接通信的表示层，而是调用业务/数据层生成/运行查询并将结果返回给业务/表示层。