Need help with nested cursors

Database
4gs
Teradata Employee

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!

Tags (2)
6 REPLIES
Senior Apprentice

Re: Need help with nested cursors

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

Fan

Re: Need help with nested cursors

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!

Enthusiast

Re: Need help with nested cursors

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

Fan

Re: Need help with nested cursors

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?

Enthusiast

Re: Need help with nested cursors

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
Fan

Re: Need help with nested cursors

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