All Forums Database
4gs 1 post Joined 07/15
20 Jul 2015
Need help with nested cursors

Hi,
How do I create a cursor within a cursor inside a stored procedure? Is it possible? There should be a cursor A and cursor B wherein cursor B uses data fetched from cursor A. I have tried a WITH RECURSIVE inside a cursor but it fails.
Thanks in advance!

dnoeth 4628 posts Joined 11/04
22 Jul 2015

I don't think there's any vaild reason to nest cursors, can you elaborate why you need it?

Dieter

madhuz 3 posts Joined 09/15
22 Sep 2015

Hi Dieter
I am piggybacking on 4gs question. I have got a requirement for a nested cursor. I have a list of 1000 names which I need to compare against another list of 1500 names using editdistance function in Teradata. so for every name in 1000 list I should get 1500 different values using editdistance and then I need to select the least value considering that would be best match. I am plannign to do this in stored proc using cursor but not sure how to loop one cursor into another. Can you please guide me through this?
 
Thanks in advance!

kirthi 65 posts Joined 02/12
22 Sep 2015

Can you try cross join of 2 tables and select Minimum on Editdistance val group by the 1000 names ?  

madhuz 3 posts Joined 09/15
22 Sep 2015
SELECT T1.NAME1,T2.NAME2, MIN(EDITDISTANCE(T1.NAME1,T2.NAME2))
FROM TABLE1 T1
CROSS JOIN TABLE2 T2
GROUP BY T1.NAME1

Thanks Kirthi for your prompt reply. The cross join solution does work but the problem is I need both the strings in my select clause but group by should be only on string in 1000 list. something like below.
Any idea how I can do that?
 

kirthi 65 posts Joined 02/12
22 Sep 2015

Please use this SQL
 

SELECT T1.NAME1,T2.NAME2, EDITDISTANCE(T1.NAME1,T2.NAME2) AS ED_VAL
FROM TABLE1 T1
CROSS JOIN TABLE2 T2
QUALIFY  ROW_NUMBER ( ) OVER (PARTITION BY T1.NAME1 ORDER BY ED_VAL) = 1

 

madhuz 3 posts Joined 09/15
23 Sep 2015

Thank you very much Kirthi. It worked as I expected. Thanks ton.

You must sign in to leave a comment.