For some reason, I can't get this query to perform as I'm getting an error: Token ( was not valid. Valid tokens: , FROM INTO. I'm running this in DB2 and I think this is occurring at either the very ...

DB2 query with multiple WITH AS cases not compiling

For some reason, I can’t get this query to perform as I’m getting an error:

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

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

I’m running this in DB2 and I think this is occurring at either the very first line of the 2nd with/as at ) , A AS ( SELECT

Where am I going wrong?

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;

 
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_timestampfrom schema.PRODUCT T1inner join schema.PRODUCT_to_product T2on t1.PRODUCT_id = t2.PRODUCT_idinner join schema.product t3on t2.product_id = t3.product_idinner join schema.product_to_job_statust t4on t3.product_id = t4.product_idinner join schema.job_statust t5on t4.job_statust_id = t5.job_statust_idwhere 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_NEXTFROM 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_MINUTESFROM AGROUP BY PRODUCT_ID;​

Answer

It is always a good idea to use an SQL Editor that supports the version of Db2 that you are using.

Looking at your SQL in IBM Data Studio with the validation set to DB2 for i 7.2, highlights the following line in red underscores

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

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

Cross referencing to the manual, I can see that LEAD is supported in Db2 for i 7.3 but not Db2 for i 7.2

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

So that would be my guess at your syntax error

Advertisement

Answer