All Forums Database
vincent_dd 5 posts Joined 09/14
08 Jun 2015
How to convert the flat procedure to set logic?

I have below procuedure but it has some performance issue. I'm trying to convert it to set logic, but not sure how to.
Can you please help?
The current procedure is like this:

DECLARE multiplier INT;
DECLARE i INT;
DECLARE cursor_Table CURSOR FOR SELECT col1,effective_Date,Term FROM Table1 ORDER BY 1;

OPEN cursor_Table;

label1:

LOOP
FETCH cursor_Table INTO cur_Table;
IF (SQLSTATE = '02000') THEN 
    LEAVE label1;
END IF;
    
SET multiplier = (CURRENT_DATE - cur_Table.effective_Date)/cur_Table.Term+1
SET i = 1;
    WHILE i <= multiplier
    DO 
    INSERT INTO Table2
    SELECT a.*, ADD_MONTHS(cur_Table.effective_Date,CAST(cur_Table.Ter m AS INT)*i)
    FROM Table1 a 
    WHERE  a.col1 = cur_Table.col1;
    
    SET i = i+1;
    END WHILE;

   
END IF;
    
END LOOP label1;
CLOSE cursor_Table;

 

 

Tags:
vincent_dd 5 posts Joined 09/14
09 Jun 2015

Any ideas? 
Thanks.

dnoeth 4628 posts Joined 11/04
09 Jun 2015

What's your Teradata release?
Can you show some actual data?
This seems to simply create rows for each month/quarter/etc based on a startdate.
You should be able to do this either using a CROSS JOIN or EXPAND ON. 

Dieter

vincent_dd 5 posts Joined 09/14
09 Jun 2015

Thanks Dieter. 

Teradata release is 14.10.04.04

Actual data is like this: 

What I have - 

Order_ID Effective_Date Term

819559512 1/30/2015 1

 

And what I want is to add a column like 'Renew Date' -

Order_ID Effective_Date Term Renew_Date

819559512 1/30/2015 1 2/30/2015

819559512 1/30/2015 1 3/30/2015

819559512 1/30/2015 1 4/30/2015

819559512 1/30/2015 1 5/30/2015

819559512 1/30/2015 1 6/30/2015

 

Now I'm using above logic to insert into a table. It's working but the performance is quite low.

Sorry I'm still new to TD, so can you please be more specific on the solution?

 

Thanks,

Vincent

dnoeth 4628 posts Joined 11/04
10 Jun 2015

Hi Vincent,
your cursor/loop logic can be replaced by a single cross join, you just need a table with numbers from 1 to the maximum possible value of "multiplier". 

CREATE TABLE numbers (i INT NOT NULL PRIMARY KEY);

INSERT INTO numbers
SELECT day_of_calendar AS i 
FROM sys_calendar.CALENDAR
WHERE day_of_calendar <= 30;

CREATE TABLE tab(col1 INT,effective_Date DATE,Term INT);
INSERT INTO tab(819559512, DATE '2015-01-30', 1);

SELECT t.*
  ,ADD_MONTHS(effective_Date,CAST(Term AS INT)*i) AS RenewDate
FROM tab AS t
CROSS JOIN numbers
WHERE i < MONTHS_BETWEEN(CURRENT_DATE, effective_Date)/Term + 1

 

Dieter

vincent_dd 5 posts Joined 09/14
10 Jun 2015

I see the light of dawn.
Thank you very much Dieter.

You must sign in to leave a comment.