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;
FETCH cursor_Table INTO cur_Table;
IF (SQLSTATE = '02000') THEN
SET multiplier = (CURRENT_DATE - cur_Table.effective_Date)/cur_Table.Term+1
SET i = 1;
WHILE i <= multiplier
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 LOOP label1;
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.
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
WHERE day_of_calendar <= 30;
CREATE TABLE tab(col1 INT,effective_Date DATE,Term INT);
INSERT INTO tab(819559512, DATE '2015-01-30', 1);
,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