For loop cursor in teradata

Question

In my Teradata Stored Procedure, I want to have a for loop cursor against a dynamic sql.

Below is the code snippet

SET get_exclude_condition = ''; SET colum_id = 'SELECT MIN (parent_criteria_id) ,MAX (parent_criteria_id) FROM arc_mdm_tbls.intnl_mtch_criteria WHERE act_ind = 1 AND criteria_typ = ''Exclude'' AND mtch_technique_id ='||mtch_technique_id||';' ; PREPARE input_stmt FROM colum_id; OPEN flex_cursor; FETCH flex_cursor INTO parent_criteria_id_min , parent_criteria_id_max ; CLOSE flex_cursor; SET get_exclude_condition = ''; WHILE (parent_criteria_id_min <= parent_criteria_id_max) DO SET get_exclude_condition = get_exclude_condition || '( '; SET for_loop_stmt = 'SELECT criteria FROM arc_mdm_tbls.intnl_mtch_criteria WHERE act_ind = 1 AND mtch_technique_id ='||mtch_technique_id||' AND criteria_typ= ''Exclude'' AND parent_criteria_id ='||parent_criteria_id_min||';'; FOR for_loop_rule AS c_cursor_rule CURSOR FOR for_loop_stmt DO

Can I declare a for loop cursor like this ? Or do I need to have something like this only ?

FOR for_loop_rule AS c_cursor_rule CURSOR FOR SELECT rule_id FROM arc_stage_tbls.assmt_scoring_rules WHERE rule_typ = :v_RuleType ORDER BY rule_id DO

I mean can I first frame the dynamic sql and then have a for loop cursor on top of that or with the cursor declaration only I need to have a static sql query ?

Please clarify.

Rob Paller · Accepted Answer · 2015-11-11 13:51:07Z

While you haven't posted everything that the stored procedure is trying to accomplish, it does appear that what you are asking can be accomplished using SET based logic and not looping through a cursor. If you need to parameterize the 'mtch_technique_id' you can use a Teradata macro which will allow you to maintain a SET based approach.

Here is the SQL for creating a macro that returns a result set based on my interpretation of what your snippet of the stored procedure is trying to accomplish:

REPLACE MACRO {MyDB}.Intnl_Mtch_Criteria(mtch_technique_id INTEGER) AS ( SELECT criteria FROM arc_mdm_tbls.intnl_mtch_criteria WHERE act_ind = 1 AND (much_technique_id, criteria_typ) IN (SELECT MIN((parent_criteria_id), MAX (parent_criteria_id) FROM arc_mdm_tbls.intnl_mtch_criteria WHERE act_ind = 1 AND criteria_typ = 'Exclude' AND mtch_technique_id = :mtch_technique_id; );

Collectives™ on Stack Overflow

For loop cursor in teradata

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related