具有多个 WITH AS 案例未编译的 DB2 查询

Question

由于某种原因，我无法执行此查询，因为我收到错误：

Token ( was not valid. Valid tokens: , FROM INTO.

我在 DB2 中运行它，我认为这发生在第 2 行的第一行with/as) , A AS ( SELECT

我哪里出错了？

WITH
RES (PRODUCT_ID, JOB_STATUS, JOB_STATUS_TIME) AS
(
select
T1.NAME,
t2.PRODUCT_id as PRODUCT_ID,
t3.product_id,
t3.created_at,
t5.name as JOB_STATUS,
t4.row_created_timestamp as JOB_STATUS_TIME ,
t3.expiration_timestamp
from schema.PRODUCT T1
inner join schema.PRODUCT_to_product T2
on t1.PRODUCT_id = t2.PRODUCT_id
inner join schema.product t3
on t2.product_id = t3.product_id
inner join schema.product_to_job_statust t4
on t3.product_id = t4.product_id
inner join schema.job_statust t5
on t4.job_statust_id = t5.job_statust_id
where t3.job_typet_id = 2
and t3.created_at >= '2019-09-20'
and t5.name <> 'D'
order by t1.name,t3.product_id
)
, A AS
(
SELECT
PRODUCT_ID, JOB_STATUS
, JOB_STATUS_TIME
, LEAD (JOB_STATUS_TIME) OVER (PARTITION BY PRODUCT_ID ORDER BY JOB_STATUS_TIME) AS JOB_STATUS_TIME_NEXT
FROM RES
)
SELECT
PRODUCT_ID
, COUNT(CASE JOB_STATUS WHEN 'O' THEN 1 END) AS CREATED
, COUNT(CASE JOB_STATUS WHEN 'C' THEN 1 END) AS COMPLETED
, COUNT(CASE JOB_STATUS WHEN 'X' THEN 1 END) AS CANCELLED
, SUM
(
CASE JOB_STATUS WHEN 'P' THEN
(DAYS(JOB_STATUS_TIME_NEXT) - DAYS(JOB_STATUS_TIME)) * 86400
+ MIDNIGHT_SECONDS(JOB_STATUS_TIME_NEXT) - MIDNIGHT_SECONDS(JOB_STATUS_TIME)
END
) / 60 AS ACTIVE_MINUTES
FROM A
GROUP BY PRODUCT_ID;

Answer 1

使用支持您正在使用的 Db2 版本的 SQL 编辑器始终是一个好主意。

在 IBM Data Studio 中查看验证设置为DB2 for i 7.2的 SQL 时，会突出显示以下行，红色下划线

, LEAD (JOB_STATUS_TIME) OVER (PARTITION BY PRODUCT_ID ORDER BY JOB_STATUS_TIME) AS JOB_STATUS_TIME_NEXT`

交叉参考手册，我可以看到 Db2 for i 7.3 支持LEAD，但 Db2 for i 7.2 不支持

https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/db2/rbafzolapexp.htm

所以这将是我对你的语法错误的猜测