How to identify all prior steps for certain step based on consistent sequence number

Analytics
Enthusiast

How to identify all prior steps for certain step based on consistent sequence number

Hi All,
I'm facing a challenge, can help me to figure it out? appreciate. here is my input table:

p_step
|step_name| seq_NO | +----+------+-------+ | ccc | aaa | 1 | | ccc | bbb | 2 | | ccc | ccc | 3 | | eee | ddd | 4 | | eee | eee | 5 |

My output would like to be:
p_step|Prior_name|
+----+------+-------+    
| ccc| aaa,bbb   |
| eee| aaa,bbb,ccc,ddd  |
thanks a lot!

 

6 REPLIES
Senior Apprentice

Re: How to identify all prior steps for certain step based on consistent sequence number

Hi,

Try the following:

WITH RECURSIVE All_steps
(p_step,prior_name,start_seqno, depth ) 
AS 
(SELECT p_step
        ,CAST('' AS VARCHAR(200)) AS prior_name
      	,MAX(seq_no)
		,0
FROM vt1
GROUP BY 1
UNION ALL
SELECT All_steps.p_step
      ,vt1.step_name||','||all_steps.prior_name
      ,vt1.seq_no
      ,depth + 1
FROM All_steps INNER JOIN vt1
ON all_steps.start_seqno = vt1.seq_no + 1
AND all_steps.Depth < 20 )
SELECT p_step
    ,RTRIM(prior_name,',')
FROM All_steps
QUALIFY start_seqno = MIN(start_seqno) OVER(PARTITION BY p_step)
ORDER BY 1,2;

Please note the following line of code:

,CAST('' AS VARCHAR(200)) AS prior_name

The length of this column needs to be long enough for the final built string (Prior_Name).

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: How to identify all prior steps for certain step based on consistent sequence number

Thank you so much Dave, I will run the query and let you know any updates. 

 

Best Regards,

Yonglin

Enthusiast

Re: How to identify all prior steps for certain step based on consistent sequence number

Hi Dave, 

the original table (vt1) actually is not directly from datasource, it's also newly created table through a certain queris. so I update me queries based on your suggestion as following (highlight in red):

 

WITH RECURSIVE All_steps (trav_oid, photostep,prior_name,start_seqno, depth)
AS (SELECT trav_oid, photostep
,CAST('' AS VARCHAR(10000)) AS prior_name
,MAX(trav_step_seq_no)
,0
FROM 

(select * from tb1 inner join tb2 on ...  ) vt
GROUP BY vt.trav_oid, vt.photostep
UNION ALL
SELECT vt.trav_oid, All_steps.photostep
,vt.step_name||','||all_steps.prior_name
,vt.trav_step_seq_no
,depth + 1
FROM All_steps INNER JOIN vt
ON all_steps.start_seqno = vt.trav_step_seq_no + 1
AND all_steps.Depth < 2000 )
SELECT photostep
,RTRIM(prior_name,',')
FROM All_steps
QUALIFY start_seqno = MIN(start_seqno) OVER(PARTITION BY photostep)
ORDER BY All_steps.trav_oid,All_steps.photostep;

 

however, I got below error message: Executed as Single statement.  Failed [3807 : 42S02] Object 'vt' does not exist. 

seens like I failed to rename the newly created table by "FROM (select * from tb1 inner join tb2 on ...  ) vt" .

do you know why? thank you so much.

 

Enthusiast

Re: How to identify all prior steps for certain step based on consistent sequence number

Apologised if I confuse you, let me ask the question in another way, my input data is a sequence of steps like below:

|step  |seq_NO|  
| aaa  |  1   |
| bbb  |  2   |
| Pcc  |  3   |
| ddd | 4 | | eee | 5 | 
| fff | 6 |
| Pgg | 7 |
.......

I want to identify all steps start with 'P' and their prior steps based on the seq_NO, for example, above input table can be transformed to below format:

|step  |seq_NO|  
| aaa  |  1   |
| bbb  |  2   |
| Pcc  |  3   |
| aaa  |  1   |
| bbb  |  2   |
| Pcc  |  3   |
| ddd | 4 | | eee | 5 | 
| fff | 6 |
| Pgg | 7 |
....
you see when I determine the prior steps for 'P%' steps, I will consider all the prior steps from Seq_no=1.

Kindly teach me how to achieve this? thanks~


 

Senior Apprentice

Re: How to identify all prior steps for certain step based on consistent sequence number

Hi,

 

I think the 'object vt does not exist' is coming from the recursive part of the query (after the UNION ALL). In your sql, vt is a derived table, but that is only available to the 'seed' SELECT (the part before the UNION ALL). It is not available to the recursive part of the request.

 

You'll need to have a table (perm or volatile) to use my code. That is what 'vt1' is in my code.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Senior Apprentice

Re: How to identify all prior steps for certain step based on consistent sequence number

Hi,

I think this will do what you want:

CREATE SET VOLATILE TABLE vt1
(step CHAR(3)
,seq_no INTEGER
)
ON COMMIT PRESERVE ROWS;

INSERT INTO vt1 VALUES('aaa',1);
INSERT INTO vt1 VALUES('bbb',2);
INSERT INTO vt1 VALUES('Pcc',3);
INSERT INTO vt1 VALUES('ddd',4);
INSERT INTO vt1 VALUES('eee',5);
INSERT INTO vt1 VALUES('fff',6);
INSERT INTO vt1 VALUES('Pgg',7); 

SELECT oth.step
    ,oth.seq_no
	,p1.seq_no
FROM vt1 AS p1
INNER JOIN vt1 AS oth
  ON p1.step LIKE 'p%'
  AND p1.seq_no >= oth.seq_no
ORDER BY  3,2;

Note that my final output has an additional column (p1.seq_no) so that you can see how the rows are grouped together.

This gives me the following output, is this what you want?

step	seq_no	seq_no
aaa	1	3
bbb	2	3
Pcc	3	3
aaa	1	7
bbb	2	7
Pcc	3	7
ddd	4	7
eee	5	7
fff	6	7
Pgg	7	7

HTH

Dave

 

 

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com