CODEWITHSUNDEEP
cursorteradatadynamic-sql
StrugglingCoder
StrugglingCoder

Reputation: 5021

For loop cursor in teradata

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.

Upvotes: 1

Views: 7681

Answers (1)

Rob Paller
Rob Paller

Reputation: 7786

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;
);

Upvotes: 1

Related Questions