I have the a table with the following structure :
path: objectId step timestamp
For example
objectId step timestamp
1 IN 2014-04-12
1 OUT 2014-04-13
3 OUT 2014-04-11
3 PLACED 2014-04-10
I need to do a query the produce the following result:
ObjectId steps
1 IN,OUT
3 PLACED,OUT
I tried the recursion as the following:
WITH TEMP(objectId,STEP,RN) AS
(
(
SELECT * FROM (
SELECT SCE.objectId
,SCE.STEP
,ROW_NUMBER() OVER ( PARTITION BY objectId order by timestamp ) as RN
FROM KAP.path SCE
WHERE SCE.STEP IN ('IN','PLACED','OUT')
) WHERE RN =1
)
UNION ALL
(
SELECT * FROM (
SELECT SCE1.objectId
, STEP
, ROW_NUMBER() OVER ( PARTITION BY objectId order by timestamp ) as RN1
FROM TEMP , path SCE1
WHERE TEMP.objectId = SCE1.objectId
AND SCE.STEP IN ('IN','PLACED','OUT')
)WHERE RN +1 = RN1
)
) select * from TEMP ORDER BY 1;
but this query failed due to
The fullselect of the recursive common table expression "DB2INST.TEMP" must be the UNION of two or more fullselects and cannot include column functions, GROUP BY clause, HAVING clause, ORDER BY clause, or an explicit join including an ON clause
could you please advise, how can i fix my query to give the desired output or any other idea more efficient?
