Teradata Stored Procedures - Introduction

• Stored Procedure is a Teradata object defined in database or user space.
   
• Stored Procedure contains two types of statements:
• SQL statements
• SPL statements(Stored Procedure Language)
   

• Stored Procedures need Perm space.
   
• Stored Procedures are compiled, Stored and Executed on the server side.
   
• The Source code can also be stored optionally

• Restrictions on using Stored Procedures:
• Stored Procedure cannot contain DDL and DCL Statements.
• Stored Procedure cannot EXPLAIN or USING statements.

• We can pass parameters to stored procedures. Parameters can be input, output or input/output. The type of parameter is indicated by the words IN, OUT or INOUT.

• IN - Values that are supplied to the stored procedure at execution
• OUT - Values that are returned from the stored procedure to the user at call completion 
• INOUT - Values that are both supplied to and returned from the stored procedure

• Stored Procedure can contain up to 1024 parameters.

• Example:

Creating a simple procedure:

CREATE PROCEDURE

TEST_PROCS.TEST_PROC

( IN var1 INTEGER, OUT var2 CHAR(40)) --> IN indicates the input parameter, OUT indicates the output parameter. Data types should be provided.

BEGIN

IF var1 = 0 THEN

SET var2 = 'THE VALUE ENTERED IS ZERO';  --> SET is used to assign values to parameters or local variables. Here we set values to an output variable.

ELSEIF var1 < 0 THEN

SET var2 = 'THE VALUE IS NEGATIVE'; --> SET also ends with a semicolon

ELSE --> IF statement may have multiple ELSEIF, but only one ELSE

SET var2 = 'THE VALUE IS POSITIVE';

END IF;  --> IF statement must be terminated with END IF

END; -->  Stored Procedure body is enclosed inside BEGIN and END. END should end with a semi colon.

Imp Note: When Using SET to assign values to parameters the Input type parameters(IN) should always be on the right side of = sign (.ie should be the sending fields)  and output (OUT)  type parameters should always be on the left side of =.(.ie should be receiving fields)

If this is not followed we would get an error.

Calling a simple procedure:

CALL TEST_PROCS.TEST_PROC(2,variable2) --> All the parameters must be represented in the CALL statement. Note that the output parameter must be specified using the parameter name(variable2 in the example). The Output Parameter value is reported similar to a column value from a table.

Result:

variable2

THE VALUE IS POSITIVE                  

• Arguments that are passed to the call statement can be defined using constants, variable or both

We can call the same procedure as shown in above example as below:

CALL SUPPORT_PROCS.TEST_PROC(3*5,Column2)

Result:

var2

THE VALUE IS POSITIVE                  

CALL SUPPORT_PROCS.TEST_PROC(-2+1,Column2)

Result:

var2

THE VALUE IS NEGATIVE                  

ANSI and Teradata Function Equivalents - Part 5 - Moving AVG(MAVG)

Function :Moving  Average

Teradata Syntax: Following is the Teradata way of  performing moving average. In the below example we have moving average window as 7 SELECT EMPLOYEEID,DEPARTMENTNO,HIREDATE,SALARY, MAVG(SALARY,7,HIREDATE) FROM EMPLOYEE; Employeeid DepartmentNo Hiredate Salary MAvg(Salary,7,Hiredate) 8 400 1/1/1992 8000 8000 4 400 1/1/1996 4000 6000 7 300 6/1/1996 7000 6333.33 3 300 1/1/1997 3000 5500 6 200 3/1/1997 6000 5600 10 200 1/1/1998 10000 6333.33 2 200 1/1/1998 2000 5714.29 5 100 2/1/1998 5000 5285.71 1 100 1/1/1999 1000 4857.14 9 100 4/1/1999 9000 5142.86 11 300 5/1/1999 11000 6285.71 12 400 8/1/1999 12000 7142.86 144 400 4/20/2013 12345.11 7477.87 124 200 4/20/2013 12345.11 8955.75 In the above result we can see that the value of moving avg is equal to the avg of the salary in the current row and 6 rows above it. When there are less than 6 rows above the current rows, average is calculated based upon the existing rows. In the above 1st column in the brackets of the MAVG indicate the column to be summed. 2nd argument indicates the window for averaging rows. In above query the window width is 7 Column names following the window width are the columns that are used for ordering . Default sequence is ascending. In above HIREDATE is used for ordering.

ANSI syntax: Following is the ANSI syntax of getting similar result as above   SELECT EMPLOYEEID,DEPARTMENTNO,HIREDATE,SALARY, AVG(SALARY) OVER (ORDER BY HIREDATE  ROWS 6 PRECEDING) FROM EMPLOYEE; Employeeid DepartmentNo Hiredate Salary Moving Avg(Salary) 8 400 1/1/1992 8000 8000 4 400 1/1/1996 4000 6000 7 300 6/1/1996 7000 6333.33 3 300 1/1/1997 3000 5500 6 200 3/1/1997 6000 5600 10 200 1/1/1998 10000 6333.33 2 200 1/1/1998 2000 5714.29 5 100 2/1/1998 5000 5285.71 1 100 1/1/1999 1000 4857.14 9 100 4/1/1999 9000 5142.86 11 300 5/1/1999 11000 6285.71 12 400 8/1/1999 12000 7142.86 124 200 4/20/2013 12345.11 7477.87 144 400 4/20/2013 12345.11 8955.75 In the above syntax: The Column specified in the brackets following keyword AVG is the column used for Averaging. Column specified by the 'ORDER BY' indicates the column used for ordering. Default is ascending. COLUMN specified by the 'ROWS N PRECEDING' indicates the number of rows above the current rows to be used for averaging . Note that this number indicates the number of rows above the current row. Thus in the above example to have a window of 7 , we need to use 'ROWS 6 PRECEDING'. However when working with MAVG we need to write the 7 to achieve a window width of 7.

ANSI and Teradata Function Equivalents - Part 4 - Moving SUM(MSUM) and MSUM with Grouping

Function : MOVING SUM

Teradata Syntax: Following is the Teradata  syntax for getting Moving Sum. In the following syntax the we get moving sum with a window of 3 SELECT EMPLOYEEID,HIREDATE,SALARY, MSUM(SALARY,3,HIREDATE) FROM EMPLOYEE; Employeeid Hiredate Salary MSum(Salary,3,Hiredate) 8 1/1/1992 8000 8000 4 1/1/1996 4000 12000 7 6/1/1996 7000 19000 3 1/1/1997 3000 14000 6 3/1/1997 6000 16000 10 1/1/1998 10000 19000 2 1/1/1998 2000 18000 5 2/1/1998 5000 17000 1 1/1/1999 1000 8000 9 4/1/1999 9000 15000 11 5/1/1999 11000 21000 12 8/1/1999 12000 32000 144 4/20/2013 12345.11 35345.11 124 4/20/2013 12345.11 36690.22 In the above result we can see that the value of moving sum is equal to the sum of the salary in the current row and 2 rows above it. In the above 1st column in the brackets of the MSUM indicate the column to be summed. 2nd argument indicates the window for summing rows. In above query the window width is 3 Column names following the window width are the columns that are used for ordering . Default sequence is ascending. In above HIREDATE is used for ordering. We can perform resets by making use of 'GROUP BY' as shown below SELECT EMPLOYEEID,HIREDATE,SALARY, MSUM(SALARY,5,HIREDATE) FROM EMPLOYEE GROUP BY DEPARTMENTNO; The column used in the 'GROUP BY' also acts as the major sort sequence and the moving sum is reset when the value of department changes.

ANSI syntax: Following is the way we get the same result as above using the ANSI syntax   SELECT EMPLOYEEID,HIREDATE,SALARY, SUM(SALARY) OVER (ORDER BY HIREDATE ROWS 2 PRECEDING) FROM EMPLOYEE; Employeeid Hiredate Salary Moving Sum(Salary) 8 1/1/1992 8000 8000 4 1/1/1996 4000 12000 7 6/1/1996 7000 19000 3 1/1/1997 3000 14000 6 3/1/1997 6000 16000 2 1/1/1998 2000 11000 10 1/1/1998 10000 18000 5 2/1/1998 5000 17000 1 1/1/1999 1000 16000 9 4/1/1999 9000 15000 11 5/1/1999 11000 21000 12 8/1/1999 12000 32000 144 4/20/2013 12345.11 35345.11 124 4/20/2013 12345.11 36690.22 In the above syntax: The Column specified in the brackets following keyword SUM is the column used for summing. Column specified by the 'ORDER BY' indicates the column used for ordering. Default is ascending. COLUMN specified by the 'ROWS N PRECEDING' indicates the number of rows above the current rows to be used for summing . Note that this number indicates the number of rows above the current row. Thus in the above example to have a window of 3 , we need to use 'ROWS 2 PRECEDING'. However when working with MSUM we need to write the 3 to achieve a window width of 3. We can perform resets by making use of 'PARTITION BY' as shown below SELECT EMPLOYEEID,HIREDATE,SALARY, SUM(SALARY) OVER (PARTITION BY DEPARTMENTNO ORDER BY HIREDATE ROWS 2 PRECEDING) FROM EMPLOYEE; The column used in the 'PARTITION BY ' also acts as the major sort sequence and the moving sum is reset when the value of department changes.

ANSI and Teradata Function Equivalents - Part 3 - Cumulative SUM(CSUM) and CSUM with Grouping

Function: Cumulative Sum

Teradata Syntax: SELECT EMPLOYEEID, HIREDATE, SALARY, CSUM(SALARY,HIREDATE) FROM EMPLOYEE; Employeeid Hiredate Salary CSum(Salary,Hiredate) 8 1/1/1992 8000 8000 4 1/1/1996 4000 12000 7 6/1/1996 7000 19000 3 1/1/1997 3000 22000 6 3/1/1997 6000 28000 10 1/1/1998 10000 38000 2 1/1/1998 2000 40000 5 2/1/1998 5000 45000 1 1/1/1999 1000 46000 9 4/1/1999 9000 55000 11 5/1/1999 11000 66000 12 8/1/1999 12000 78000 144 4/20/2013 12345.11 90345.11 124 4/20/2013 12345.11 102690.2 In Teradata Syntax: The 1st column in the brackets indicates the column to be Summed Columns following in the 1st column in brackets indicate the columns used for sorting. BY default its ASCENDING.

ANSI syntax: Following is the way we write ANSI syntax to get same result: SELECT EMPLOYEEID,HIREDATE,SALARY, SUM(SALARY) OVER (ORDER BY HIREDATE ROWS UNBOUNDED PRECEDING) FROM EMPLOYEE;   Employeeid Hiredate Salary Cumulative Sum(Salary) 8 1/1/1992 8000 8000 4 1/1/1996 4000 12000 7 6/1/1996 7000 19000 3 1/1/1997 3000 22000 6 3/1/1997 6000 28000 2 1/1/1998 2000 30000 10 1/1/1998 10000 40000 5 2/1/1998 5000 45000 1 1/1/1999 1000 46000 9 4/1/1999 9000 55000 11 5/1/1999 11000 66000 12 8/1/1999 12000 78000 144 4/20/2013 12345.11 90345.11 124 4/20/2013 12345.11 102690.2 In ANSI syntax: The column do be used for summing up is enclosed in the brackets following keyword SUM. Note that when we used RANK, we did not write any column in the brackets following the keyword RANK. Column used for ordering is specified by the 'ORDER BY' clause. Defaulting sequence is ASCENDING. ROWS UNBOUNDED PRECEEDING indicates that all the rows preceding the current row (including current row) are to be used for summation. This generate Cumulative Summation.

Function : Cumulative SUM with grouping

Teradata Syntax:   Following is the way we perform cumulative sum with grouping (also called cumulative sum with reset) SELECT EMPLOYEEID,HIREDATE,DEPARTMENTNO,SALARY, CSUM(SALARY,HIREDATE) FROM EMPLOYEE GROUP BY DEPARTMENTNO; Employeeid Hiredate DepartmentNo Salary CSum(Salary,Hiredate) 5 2/1/1998 100 5000 5000 1 1/1/1999 100 1000 6000 9 4/1/1999 100 9000 15000 6 3/1/1997 200 6000 6000 10 1/1/1998 200 10000 16000 2 1/1/1998 200 2000 18000 124 4/20/2013 200 12345.11 30345.11 7 6/1/1996 300 7000 7000 3 1/1/1997 300 3000 10000 11 5/1/1999 300 11000 21000 8 1/1/1992 400 8000 8000 4 1/1/1996 400 4000 12000 12 8/1/1999 400 12000 24000 144 4/20/2013 400 12345.11 36345.11 Group by indicates the column to be used for resetting the cumulative sum. When the column changes ( in above example as the department changes ) the accumulated value is discarded. Note that Column indicated by group by also indicates the major sorting sequence. The output is sorted by this column used for grouping.

ANSI syntax: Following is how we use ANSI syntax to perform cumulative sum with reset SELECT EMPLOYEEID,HIREDATE,DEPARTMENTNO,SALARY, SUM(SALARY) OVER (PARTITION BY DEPARTMENTNO ORDER BY HIREDATE ROWS UNBOUNDED PRECEDING) FROM EMPLOYEE; Employeeid Hiredate DepartmentNo Salary Cumulative Sum(Salary) 5 2/1/1998 100 5000 5000 1 1/1/1999 100 1000 6000 9 4/1/1999 100 9000 15000 6 3/1/1997 200 6000 6000 10 1/1/1998 200 10000 16000 2 1/1/1998 200 2000 18000 124 4/20/2013 200 12345.11 30345.11 7 6/1/1996 300 7000 7000 3 1/1/1997 300 3000 10000 11 5/1/1999 300 11000 21000 8 1/1/1992 400 8000 8000 4 1/1/1996 400 4000 12000 12 8/1/1999 400 12000 24000 144 4/20/2013 400 12345.11 36345.11 PARTITION BY indicates the column(s) to be used for grouping. In the above example as the DEPARTMENTNO changes the cumulative sum is reset.