SQL To Create a Savings Table
The following code, Savings.sql, creates a savings table and inserts test data into the table:
DROP TABLE Savings;
CREATE TABLE Savings
,NO BEFORE JOURNAL
,NO AFTER JOURNAL
(
AccountType CHAR(1) NOT NULL
,AccountNumber CHAR(20) NOT NULL
,OpeningBalance DECIMAL(9,3)
,AccruedInterest DECIMAL(9,3)
,LastYearEnd DATE
,LastAccrualDate DATE
)
UNIQUE PRIMARY INDEX( AccountType,AccountNumber );
INSERT INTO Savings ('S','1200121',10000.00,0,'2012-12-31','2013-01-01');
INSERT INTO Savings ('S','1200122',20000.00,0,'2012-12-31','2013-01-01');
INSERT INTO Savings ('S','1200123',30000.00,0,'2012-12-31','2013-01-01');
INSERT INTO Savings ('S','1200124',40000.00,0,'2012-12-31','2013-01-01');
INSERT INTO Savings ('S','1200125',50000.00,0,'2012-12-31','2013-01-01');
INSERT INTO Savings ('C','1200121', 1000.00,0,'2012-12-31','2013-01-01');
INSERT INTO Savings ('C','1200122', 2000.00,0,'2012-12-31','2013-01-01');
INSERT INTO Savings ('C','1200123', 3000.00,0,'2012-12-31','2013-01-01');
INSERT INTO Savings ('C','1200124', 4000.00,0,'2012-12-31','2013-01-01');
INSERT INTO Savings ('C','1200125', 5000.00,0,'2012-12-31','2013-01-01');
Stored Procedure to Update Accrued Interest
The following stored procedure, SP_Accrue_Interest.sql, uses a cursor to select qualified records and update accrued interest.
REPLACE PROCEDURE SP_Accrue_Interest(IN Arg_AccontType CHAR(20), OUT Proc_Msg VARCHAR(80))
BEGIN
DECLARE pv_AccountType CHAR(1);
DECLARE pv_AccountNumber CHAR(20);
DECLARE pv_CalcBase DEC(9,2);
DECLARE pv_Interest DEC(9,2);
DECLARE pv_ProcCount INT;
DECLARE calc_cursor CURSOR FOR
SELECT AccountType, AccountNumber, (OpeningBalance+AccruedInterest)
AS BaseAmount FROM Savings
WHERE (AccountType = :Arg_AccontType
OR 'A '= :Arg_AccontType)
AND LastAccrualDate< DATE;
OPEN calc_cursor;
FETCH calc_cursor INTO pv_AccountType, pv_AccountNumber,pv_CalcBase;
SET pv_ProcCount = 0;
SET Proc_Msg = 'Processed ' || pv_ProcCount || ' records';
Calc_Loop:
WHILE (SQLCODE =0) DO
SET pv_ProcCount = pv_ProcCount + 1;
SET Proc_Msg = 'Processed ' || pv_ProcCount || ' records';
SET pv_Interest = pv_CalcBase * 0.010;
UPDATE Savings
SET AccruedInterest = AccruedInterest + :pv_Interest
,LastAccrualDate = DATE
WHERE AccountType = :pv_AccountType
AND AccountNumber = :pv_AccountNumber;
FETCH calc_cursor INTO pv_AccountType, pv_AccountNumber,pv_CalcBase;
END WHILE;
CLOSE calc_cursor;
END;
BTEQ Script to Call the Stored Procedure
The following BTEQ script, Bld_iudf.btq, calls the stored procedure and performs SELECTs from the savings table to test the stored procedure.
.SET WIDTH 240
.logon TDSystem/MyUserId,MyPass
.REMARK 'Choosing database';
DATABASE Test_Database;
.REMARK 'Building & populating savings table';
.RUN FILE=./Savings.sql
.REMARK 'Building stored procedure';
.COMPILE FILE=./SP_Accrue_Interest.sql
.REMARK 'Testing stored procedure';
SELECT * FROM Savings ORDER BY 1,2;
CALL SP_Accrue_Interest('C',"Messg");
SELECT * FROM Savings ORDER BY 1,2;
CALL SP_Accrue_Interest('S',"Messg");
SELECT * FROM Savings ORDER BY 1,2;
CALL SP_Accrue_Interest('C',"Messg");
SELECT * FROM Savings ORDER BY 1,2;
CALL SP_Accrue_Interest('S',"Messg");
SELECT * FROM Savings ORDER BY 1,2;
.REMARK 'Rebuilding & populating savings table';
.RUN FILE=./Savings.sql
SELECT * FROM Savings ORDER BY 1,2;
CALL SP_Accrue_Interest('A',"Messg");
SELECT * FROM Savings ORDER BY 1,2;
CALL SP_Accrue_Interest('A',"Messg");
SELECT * FROM Savings ORDER BY 1,2;
.REMARK 'Finished testing stored procedure';
.LOGOFF
.EXIT 0
Linux Code To Call the BTEQ Script
This Linux code calls the BTEQ script:
bteq bld_iudfs.btq 2>&1 | tee bld_iudfs.btq.i1.out
Output of the Stored Procedure
BTEQ 14.00.00.02 Wed Apr 24 09:07:08 2013 +---------+---------+---------+---------+---------+---------+---------+---------+---------+ .SET WIDTH 240 +---------+---------+---------+---------+---------+---------+---------+---------+---------+ .logon TDSystem/MyUserId, *** Logon successfully completed. *** Teradata Database Release is 14.10i.00.434 *** Teradata Database Version is 14.10i.00.434 *** Transaction Semantics are BTET. *** Session Character Set Name is 'ASCII'. *** Total elapsed time was 1 second. +---------+---------+---------+---------+---------+---------+---------+---------+---------+ .REMARK 'Choosing database'; Choosing database +---------+---------+---------+---------+---------+---------+---------+---------+---------+ DATABASE Felix_Test; *** New default database accepted. *** Total elapsed time was 1 second. +---------+---------+---------+---------+---------+---------+---------+---------+---------+ .REMARK 'Populating savings table'; Populating savings table +---------+---------+---------+---------+---------+---------+---------+---------+---------+ .RUN FILE=./SavingsData.sql +---------+---------+---------+---------+---------+---------+---------+---------+---------+ DELETE FROM Savings ALL; *** Delete completed. 10 rows removed. *** Total elapsed time was 2 seconds. +---------+---------+---------+---------+---------+---------+---------+---------+---------+ INSERT INTO Savings ('S','1200121',10000.00,0,'2012-12-31','2013-01-01'); *** Insert completed. One row added. *** Total elapsed time was 3 seconds. +---------+---------+---------+---------+---------+---------+---------+---------+---------+ INSERT INTO Savings ('S','1200122',20000.00,0,'2012-12-31','2013-01-01'); *** Insert completed. One row added. *** Total elapsed time was 1 second. +---------+---------+---------+---------+---------+---------+---------+---------+---------+ INSERT INTO Savings ('S','1200123',30000.00,0,'2012-12-31','2013-01-01'); *** Insert completed. One row added. *** Total elapsed time was 1 second. +---------+---------+---------+---------+---------+---------+---------+---------+---------+ INSERT INTO Savings ('S','1200124',40000.00,0,'2012-12-31','2013-01-01'); *** Insert completed. One row added. *** Total elapsed time was 1 second. +---------+---------+---------+---------+---------+---------+---------+---------+---------+ INSERT INTO Savings ('S','1200125',50000.00,0,'2012-12-31','2013-01-01'); *** Insert completed. One row added. *** Total elapsed time was 1 second. +---------+---------+---------+---------+---------+---------+---------+---------+---------+ INSERT INTO Savings ('C','1200121', 1000.00,0,'2012-12-31','2013-01-01'); *** Insert completed. One row added. *** Total elapsed time was 1 second. +---------+---------+---------+---------+---------+---------+---------+---------+---------+ INSERT INTO Savings ('C','1200122', 2000.00,0,'2012-12-31','2013-01-01'); *** Insert completed. One row added. *** Total elapsed time was 1 second. +---------+---------+---------+---------+---------+---------+---------+---------+---------+ INSERT INTO Savings ('C','1200123', 3000.00,0,'2012-12-31','2013-01-01'); *** Insert completed. One row added. *** Total elapsed time was 1 second. +---------+---------+---------+---------+---------+---------+---------+---------+---------+ INSERT INTO Savings ('C','1200124', 4000.00,0,'2012-12-31','2013-01-01'); *** Insert completed. One row added. *** Total elapsed time was 1 second. +---------+---------+---------+---------+---------+---------+---------+---------+---------+ INSERT INTO Savings ('C','1200125', 5000.00,0,'2012-12-31','2013-01-01'); *** Insert completed. One row added. *** Total elapsed time was 1 second. +---------+---------+---------+---------+---------+---------+---------+---------+---------+ *** Warning: EOF on INPUT stream. +---------+---------+---------+---------+---------+---------+---------+---------+---------+ .REMARK 'Testing internal UDF'; Testing internal UDF +---------+---------+---------+---------+---------+---------+---------+---------+---------+- SELECT * FROM Savings ORDER BY 1,2; *** Query completed. 10 rows found. 6 columns returned. *** Total elapsed time was 6 seconds. AccountType AccountNumber OpeningBalance AccruedInterest LastYearEnd LastAccrualDate ----------- --------------- -------------- --------------- ----------- --------------- C 1200121 1000.000 .000 12/12/31 13/01/01 C 1200122 2000.000 .000 12/12/31 13/01/01 C 1200123 3000.000 .000 12/12/31 13/01/01 C 1200124 4000.000 .000 12/12/31 13/01/01 C 1200125 5000.000 .000 12/12/31 13/01/01 S 1200121 10000.000 .000 12/12/31 13/01/01 S 1200122 20000.000 .000 12/12/31 13/01/01 S 1200123 30000.000 .000 12/12/31 13/01/01 S 1200124 40000.000 .000 12/12/31 13/01/01 S 1200125 50000.000 .000 12/12/31 13/01/01 +---------+---------+---------+---------+---------+---------+---------+---------+---------+ CALL SP_Accrue_Interest('C',"Messg"); *** Procedure has been executed. *** Total elapsed time was 2 seconds. Proc_Msg -------------------------------------------------------------------------------- Processed 5 records +---------+---------+---------+---------+---------+---------+---------+---------+---------+ SELECT * FROM Savings ORDER BY 1,2; *** Query completed. 10 rows found. 6 columns returned. *** Total elapsed time was 1 second. AccountType AccountNumber OpeningBalance AccruedInterest LastYearEnd LastAccrualDate ----------- --------------- -------------- --------------- ----------- --------------- C 1200121 1000.000 10.000 12/12/31 13/04/24 C 1200122 2000.000 20.000 12/12/31 13/04/24 C 1200123 3000.000 30.000 12/12/31 13/04/24 C 1200124 4000.000 40.000 12/12/31 13/04/24 C 1200125 5000.000 50.000 12/12/31 13/04/24 S 1200121 10000.000 .000 12/12/31 13/01/01 S 1200122 20000.000 .000 12/12/31 13/01/01 S 1200123 30000.000 .000 12/12/31 13/01/01 S 1200124 40000.000 .000 12/12/31 13/01/01 S 1200125 50000.000 .000 12/12/31 13/01/01 +---------+---------+---------+---------+---------+---------+---------+---------+---------+ CALL SP_Accrue_Interest('S',"Messg"); *** Procedure has been executed. *** Total elapsed time was 1 second. Proc_Msg -------------------------------------------------------------------------------- Processed 5 records +---------+---------+---------+---------+---------+---------+---------+---------+---------+ SELECT * FROM Savings ORDER BY 1,2; *** Query completed. 10 rows found. 6 columns returned. *** Total elapsed time was 1 second. AccountType AccountNumber OpeningBalance AccruedInterest LastYearEnd LastAccrualDate ----------- --------------- -------------- --------------- ----------- --------------- C 1200121 1000.000 10.000 12/12/31 13/04/24 C 1200122 2000.000 20.000 12/12/31 13/04/24 C 1200123 3000.000 30.000 12/12/31 13/04/24 C 1200124 4000.000 40.000 12/12/31 13/04/24 C 1200125 5000.000 50.000 12/12/31 13/04/24 S 120012 10000.000 100.000 12/12/31 13/04/24 S 120012 20000.000 200.000 12/12/31 13/04/24 S 120012 30000.000 300.000 12/12/31 13/04/24 S 12001 40000.000 400.000 12/12/31 13/04/24 S 12001 50000.000 500.000 12/12/31 13/04/24 +---------+---------+---------+---------+---------+---------+---------+---------+---------+ CALL SP_Accrue_Interest('C',"Messg"); *** Procedure has been executed. *** Total elapsed time was 1 second. Proc_Msg -------------------------------------------------------------------------------- Processed 0 records +---------+---------+---------+---------+---------+---------+---------+---------+---------+ SELECT * FROM Savings ORDER BY 1,2; *** Query completed. 10 rows found. 6 columns returned. *** Total elapsed time was 1 second. AccountType AccountNumber OpeningBalance AccruedInterest LastYearEnd LastAccrualDate ----------- --------------- -------------- --------------- ----------- --------------- C 1200121 1000.000 10.000 12/12/31 13/04/24 C 1200122 2000.000 20.000 12/12/31 13/04/24 C 1200123 3000.000 30.000 12/12/31 13/04/24 C 1200124 4000.000 40.000 12/12/31 13/04/24 C 1200125 5000.000 50.000 12/12/31 13/04/24 S 1200121 10000.000 100.000 12/12/31 13/04/24 S 1200122 20000.000 200.000 12/12/31 13/04/24 S 1200123 30000.000 300.000 12/12/31 13/04/24 S 1200124 40000.000 400.000 12/12/31 13/04/24 S 1200125 50000.000 500.000 12/12/31 13/04/24 +---------+---------+---------+---------+---------+---------+---------+---------+---------+ CALL SP_Accrue_Interest('S',"Messg"); *** Procedure has been executed. *** Total elapsed time was 1 second. Proc_Msg -------------------------------------------------------------------------------- Processed 0 records +---------+---------+---------+---------+---------+---------+---------+---------+---------+ SELECT * FROM Savings ORDER BY 1,2; *** Query completed. 10 rows found. 6 columns returned. *** Total elapsed time was 4 seconds. AccountType AccountNumber OpeningBalance AccruedInterest LastYearEnd LastAccrualDate ----------- --------------- -------------- --------------- ----------- --------------- C 1200121 1000.000 10.000 12/12/31 13/04/24 C 1200122 2000.000 20.000 12/12/31 13/04/24 C 1200123 3000.000 30.000 12/12/31 13/04/24 C 1200124 4000.000 40.000 12/12/31 13/04/24 C 1200125 5000.000 50.000 12/12/31 13/04/24 S 1200121 10000.000 100.000 12/12/31 13/04/24 S 1200122 20000.000 200.000 12/12/31 13/04/24 S 1200123 30000.000 300.000 12/12/31 13/04/24 S 1200124 40000.000 400.000 12/12/31 13/04/24 S 1200125 50000.000 500.000 12/12/31 13/04/24 +---------+---------+---------+---------+---------+---------+---------+---------+---------+ .REMARK 'Populating savings table'; Populating savings table +---------+---------+---------+---------+---------+---------+---------+---------+---------+ .RUN FILE=./SavingsData.sql +---------+---------+---------+---------+---------+---------+---------+---------+---------+ DELETE FROM Savings ALL; *** Delete completed. 10 rows removed. *** Total elapsed time was 2 seconds. +---------+---------+---------+---------+---------+---------+---------+---------+---------+ INSERT INTO Savings ('S','1200121',10000.00,0,'2012-12-31','2013-01-01'); *** Insert completed. One row added. *** Total elapsed time was 1 second. +---------+---------+---------+---------+---------+---------+---------+---------+---------+ INSERT INTO Savings ('S','1200122',20000.00,0,'2012-12-31','2013-01-01'); *** Insert completed. One row added. *** Total elapsed time was 1 second. +---------+---------+---------+---------+---------+---------+---------+---------+---------+ INSERT INTO Savings ('S','1200123',30000.00,0,'2012-12-31','2013-01-01'); *** Insert completed. One row added. *** Total elapsed time was 1 second. +---------+---------+---------+---------+---------+---------+---------+---------+---------+ INSERT INTO Savings ('S','1200124',40000.00,0,'2012-12-31','2013-01-01'); *** Insert completed. One row added. *** Total elapsed time was 1 second. +---------+---------+---------+---------+---------+---------+---------+---------+---------+ INSERT INTO Savings ('S','1200125',50000.00,0,'2012-12-31','2013-01-01'); *** Insert completed. One row added. *** Total elapsed time was 1 second. +---------+---------+---------+---------+---------+---------+---------+---------+---------+ INSERT INTO Savings ('C','1200121', 1000.00,0,'2012-12-31','2013-01-01'); *** Insert completed. One row added. *** Total elapsed time was 1 second. +---------+---------+---------+---------+---------+---------+---------+---------+---------+ INSERT INTO Savings ('C','1200122', 2000.00,0,'2012-12-31','2013-01-01'); *** Insert completed. One row added. *** Total elapsed time was 1 second. +---------+---------+---------+---------+---------+---------+---------+---------+---------+ INSERT INTO Savings ('C','1200123', 3000.00,0,'2012-12-31','2013-01-01'); *** Insert completed. One row added. *** Total elapsed time was 1 second. +---------+---------+---------+---------+---------+---------+---------+---------+---------+ INSERT INTO Savings ('C','1200124', 4000.00,0,'2012-12-31','2013-01-01'); *** Insert completed. One row added. *** Total elapsed time was 1 second. +---------+---------+---------+---------+---------+---------+---------+---------+---------+ INSERT INTO Savings ('C','1200125', 5000.00,0,'2012-12-31','2013-01-01'); *** Insert completed. One row added. *** Total elapsed time was 1 second. +---------+---------+---------+---------+---------+---------+---------+---------+---------+ *** Warning: EOF on INPUT stream. +---------+---------+---------+---------+---------+---------+---------+---------+---------+ SELECT * FROM Savings ORDER BY 1,2; *** Query completed. 10 rows found. 6 columns returned. *** Total elapsed time was 1 second. AccountType AccountNumber OpeningBalance AccruedInterest LastYearEnd LastAccrualDate ----------- --------------- -------------- --------------- ----------- --------------- C 1200121 1000.000 .000 12/12/31 13/01/01 C 1200122 2000.000 .000 12/12/31 13/01/01 C 1200123 3000.000 .000 12/12/31 13/01/01 C 1200124 4000.000 .000 12/12/31 13/01/01 C 1200125 5000.000 .000 12/12/31 13/01/01 S 1200121 10000.000 .000 12/12/31 13/01/01 S 1200122 20000.000 .000 12/12/31 13/01/01 S 1200123 30000.000 .000 12/12/31 13/01/01 S 1200124 40000.000 .000 12/12/31 13/01/01 S 1200125 50000.000 .000 12/12/31 13/01/01 +---------+---------+---------+---------+---------+---------+---------+---------+---------+ CALL SP_Accrue_Interest('A',"Messg"); *** Procedure has been executed. *** Total elapsed time was 1 second. Proc_Msg -------------------------------------------------------------------------------- Processed 10 records +---------+---------+---------+---------+---------+---------+---------+---------+---------+ SELECT * FROM Savings ORDER BY 1,2; *** Query completed. 10 rows found. 6 columns returned. *** Total elapsed time was 1 second. AccountType AccountNumber OpeningBalance AccruedInterest LastYearEnd LastAccrualDate ----------- --------------- -------------- --------------- ----------- --------------- C 1200121 1000.000 10.000 12/12/31 13/04/24 C 1200122 2000.000 20.000 12/12/31 13/04/24 C 1200123 3000.000 30.000 12/12/31 13/04/24 C 1200124 4000.000 40.000 12/12/31 13/04/24 C 1200125 5000.000 50.000 12/12/31 13/04/24 S 1200121 10000.000 100.000 12/12/31 13/04/24 S 1200122 20000.000 200.000 12/12/31 13/04/24 S 1200123 30000.000 300.000 12/12/31 13/04/24 S 1200124 40000.000 400.000 12/12/31 13/04/24 S 1200125 50000.000 500.000 12/12/31 13/04/24 +---------+---------+---------+---------+---------+---------+---------+---------+---------+ CALL SP_Accrue_Interest('A',"Messg"); *** Procedure has been executed. *** Total elapsed time was 1 second. Proc_Msg -------------------------------------------------------------------------------- Processed 0 records +---------+---------+---------+---------+---------+---------+---------+---------+---------+ SELECT * FROM Savings ORDER BY 1,2; *** Query completed. 10 rows found. 6 columns returned. *** Total elapsed time was 1 second. AccountType AccountNumber OpeningBalance AccruedInterest LastYearEnd LastAccrualDate ----------- --------------- -------------- --------------- ----------- --------------- C 1200121 1000.000 10.000 12/12/31 13/04/24 C 1200122 2000.000 20.000 12/12/31 13/04/24 C 1200123 3000.000 30.000 12/12/31 13/04/24 C 1200124 4000.000 40.000 12/12/31 13/04/24 C 1200125 5000.000 50.000 12/12/31 13/04/24 S 1200121 10000.000 100.000 12/12/31 13/04/24 S 1200122 20000.000 200.000 12/12/31 13/04/24 S 1200123 30000.000 300.000 12/12/31 13/04/24 S 1200124 40000.000 400.000 12/12/31 13/04/24 S 1200125 50000.000 500.000 12/12/31 13/04/24 +---------+---------+---------+---------+---------+---------+---------+---------+---------+ .REMARK 'Finishied testing internal UDF'; Finishied testing internal UDF +---------+---------+---------+---------+---------+---------+---------+---------+---------+ .LOGOFF *** You are now logged off from the DBC. +---------+---------+---------+---------+---------+---------+---------+---------+---------+ .EXIT 0 *** Exiting BTEQ... *** RC (return code) = 0