Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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 first line of the 2nd with/as at ) , A AS ( SELECT
Where am I going wrong?
  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)
  ) / 60 AS ACTIVE_MINUTES
FROM A
GROUP BY PRODUCT_ID;
                Remove the order by.  It does not belong in a CTE (or subquery).  I don't know if DB2 checks for that, but it could also be the cause of the error.
– Gordon Linoff
                Oct 4, 2019 at 17:08
                Your field list for RES doesn't match its SELECT statement and your A query doesn't have a field list at all...
– Hellion
                Oct 4, 2019 at 17:20
                So even by adding a field list to A or removing from RES I get the same error. This is on IBM iSeries version 7 too
– Geoff_S
                Oct 4, 2019 at 17:39
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`
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
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.