How to convert the flat procedure to set logic?

Database
Enthusiast

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.Term 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 (1)
5 REPLIES
Enthusiast

Re: How to convert the flat procedure to set logic?

Any ideas? 

Thanks.

Senior Apprentice

Re: How to convert the flat procedure to set logic?

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. 

Enthusiast

Re: How to convert the flat procedure to set logic?

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

Senior Apprentice

Re: How to convert the flat procedure to set logic?

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
Enthusiast

Re: How to convert the flat procedure to set logic?

I see the light of dawn.

Thank you very much Dieter.