DATE Functions

As part of Domain Specific Functions Teradata database 14 has released new DATE functions.
Below re list of function.

Function	Description
LAST_DAY	Return date of the last day of the month that contains timestamp value
NEXT_DAY	returns first weekday named by day_value that is later than the date specified by date/timestamp value
NUMTODSINTERVAL	convert a numeric value into an INTERVAL DAY(4) TO SECOND(6) value
NUMTOYMINTERVAL	Convert a numeric value into an INTERVAL YEAR(4) TO MONTH value
TO_DSINTERVAL	Convert a string value into an INTERVAL DAY(4) TO SECOND(6) value.
TO_YMINTERVAL	Convert a string value into an INTERVAL YEAR(4) TO MONTH value.
MONTHS_BETWEEN	Return the number of months between two date/timestamp values.
OADD_MONTHS	Add a specified date/timestamp value to a specified number of months and return the resulting date.
TO_DATE	Convert a string into a DATE value via a format string.
TO_TIMESTAMP	Convert a string into a TIMESTAMP value via a format string.
TO_TIMESTAMP_TZ	Convert a string into a TIMESTAMP WITH TIME ZONE value via a format string.
TRUNC	Returns a DATE value with the time portion truncated to the unit specified by a format string.
ROUND	Returns a DATE value with the time portion rounded to the unit specified by a format string.

Examples:

SELECT LAST_DAY (DATE);
-----------------------
LAST_DAY(Date)
2014-06-30


SELECT NEXT_DAY(DATE '2014-06-10' , 'FRIDAY');
----------------------------------------------
NEXT_DAY(2014-06-10,'FRIDAY')
2014-06-13


SELECT NUMTODSINTERVAL(86405,'SECOND'), NUMTOYMINTERVAL(100, 'MONTH' );
----------------------------------------------------------------------------------------------------------------------
NUMTODSINTERVAL(86405,'SECOND')               NUMTOYMINTERVAL(100,'MONTH')
  1 00:00:05.000000                                                     8-04


SELECT TO_DSINTERVAL('150 08:30:00') , TO_YMINTERVAL( '2-11') ;
----------------------------------------------------------------------------------------------
TO_DSINTERVAL('150 08:30:00')                            TO_YMINTERVAL('2-11')
150 08:30:00.000000                                                       2-11
The above functions can be helpful while adding to any date columns.

samples
SELECT ORDER_DATE, ORDER_DATE + TO_YMINTERVAL('02-11') FROM ORDERS;
SELECT EMP_ID, LAST_NAME FROM EMP_TABLE  WHERE HIRE_DATE + TO_DSINTERVAL('100 00:00:00')


SELECT MONTHS_BETWEEN(DATE'2014-06-01', DATE'2014-02-01');
-------------------------------------------------------------
MONTHS_BETWEEN(2014-06-01,2014-02-01)
4.00


SELECT OADD_MONTHS (DATE '2014-04-15', 2), OADD_MONTHS (DATE '2008-02-29', 1);
---------------------------------------------------------------------------------------------------------------------
OADD_MONTHS(2014-04-15,2)                              OADD_MONTHS(2008-02-29,1)
2014-06-15                                             2008-03-31
Since 29 is the last day in February, March 31 is returned since 31 is the last day in March


SELECT TRUNC(CAST('2014/06/05' AS DATE), 'D') (FORMAT 'yyyy-mm-dd');
----------------------------------------------------------------------------------------------------
TRUNC('2014/06/05','D')
2014-06-01
The date was rounded to the first day of that week.


SELECT ROUND(CAST('2003/09/20' AS DATE), 'RM') (FORMAT 'yyyy-mm-dd');
----------------------------------------------------------------------------------------------------------
ROUND('2003/09/20','RM')
2003-10-01
Since the day is greater than or equal to 16, the date is rounded to the beginning of the next month.

Continue Reading...

Numeric Functions

Teradata 14 has released many Domain Specific Functions. Now we are discussing about new Numeric functions which are equivalent to Oracle.
The following numeric functions are available in this Teradata 14 release.

Name	Description
SIGN	It returns the sign of a value
TRUNC	It will truncate a numeric value
ROUND	It will round a numeric value
GREATEST	It return the highest value from a list of given values
LEAST	It return the lowest value from a list of given values
TO_NUMBER	It convert a string to a number via a format string
CEILING	It return the smallest integer not less than the input parameter
FLOOR	It return the largest integer equal to or less than the input parameter

Example for all functions.

SELECT
  SIGN(-123)                                         as SIGN
, TRUNC (32.976)                                as TRUNC
, ROUND(345.175)                               as ROUND
, GREATEST(12,56,10.1, 155.6)          as GREATEST
, LEAST (12,56,10.1, 155.6)                 as LEAST
, TO_NUMBER ('4769.96', '9999.99')  as TO_NUMBER
, CEIL( 5.4)                                            as CEIL
, FLOOR(3.86)                                      as FLOOR
;

Continue Reading...

Regular Expression Functions

In this Teradata 14 has released many domain specific function added NUMERIC data type, String functions and many of the functions supports regular expressions. These new functions are designed to be compatible to Oracle.
Here we are going to discuss about the following domain specific regular expression functions.

1. REGEXP_SUBSTR
2. REGEXP_REPLACE 
3. REGEXP_INSTR 
4. REGEXP_SIMILAR
5. REGEXP_SPLIT_TO_TABLE

Now will discuss in detail of each function below

REGEXP_SUBSTR
This function extracts a substring from a source string that matches a regular expression pattern.

General Syntax:
REGEXP_SUBSTR (source_string, regexp_string, position_arg);

position_arg = (occurance_org, match_arg)

REGEXP_SUBSTR Function Example:

SELECT REGEXP_SUBSTR ('God Bless Mummy God Bless Daddy', 'bless', 1, 2, 'i')

In the above example
source_string = God Bless Mummy God Bless Daddy
regexp_string = Bless
position_arg = Find the second occurrence of the string that matches regular expression. 'i' indicates case insensitive.



REGEXP_REPLACE
This function replaces portions of the source string parameter that match a regular expression pattern with a replace string.

General Syntax:
REGEXP_SUBSTR (source_string, regexp_string, replace_string);

replace_string=(position_arg,occurance_org, match_arg)

REGEXP_REPLACE Function Examples:

SELECT REGEXP_REPLACE ('Hello World World', 'world', 'My', 1, 1,'i');
In this we are replacing the 1st occurrence of 'World' with 'My' by ignoring case specific.

SELECT REGEXP_REPLACE ('Godís Love is so wonderful.', 'God's', 'Mother', 1, 1, 'c');

In this we are replacing the 1st occurrence of 'God's' with 'Mother' by considering with case specific with 'c'.

SELECT REGEXP_REPLACE('I love the buzz-buzz buzzing of the bee', 'buzz', 'BUZZ', 1, 2, 'c');
In this we are replacing the 2nd occurrence of 'buzz' with 'BUZZ' by considering the case specific with 'c'.

SELECT REGEXP_REPLACE ('ABCD123-$567xy','[^0-9]*','',1,0,'i');
In this we are replacing the any character or symbols with NULL. So it results only Numeric values.

REGEXP_INSTR
This function Search the source string for a match to a regular expression pattern and return the beginning or ending position of that match.

General Syntax:
REGEXP_SUBSTR (source_string, regexp_string, position_arg)

position_argt = (occurance_org,return_opt, match_arg)

return_opt  returns  0 = function returns the beginning position of the match (default).
                               1 = function returns the end position

REGEXP_INSTR Function Examples:

SELECT REGEXP_INSTR('Happy Birthday to you', 'Happy Birthday', 1, 1, 0, 'c');
It returns 1

SELECT REGEXP_INSTR('Happy Birthday to you', 'Happy Birthday', 1, 1, 1, 'c');
It returns 15

REGEXP_SIMILAR
This function compares a source string to a regular expression and returns an integer value.
 1 (true) if the entire string matches regexp_arg
 0 (false) if the entire string does not match regexp_arg

General Snytax:
REGEXP_SIMILAR(source_string, regexp_string, match_arg)

REGEXP_INSTR Function Examples:

SELECT    Emp_Name
FROM    Emp_Table
WHERE    REGEXP_SIMILAR (emp_name, '(Smith B(i|y)rd)|| (John B(i|y)rd)','c') = 1;

It returns the names of employees that match
Smith Byrd
Smith Bird
John Bird
John Byrd

REGEXP_SPLIT_TO_TABLE
This table function splits a source string into a table of strings using a regular expression as the delimiter.

General Snytax:
REGEXP_SPLIT_TO_TABLE (source_string, regexp_string, match_arg)

REGEXP_SPLIT_TO_TABLE Function Examples:

CREATE TABLE split_table_latin(id integer, src varchar(100) character set latin, pattern varchar(100) character set latin, match varchar(100) character set latin);

INSERT into split_table_latin(3, 'The2134quick234brown234fox987jumps8743over342the03487lazy089734red972384dog2343.', '[0-9]*','c');

Then the following SELECT statement:

SELECT * from table ( regexp_split_to_table(split_table_latin.src,split_table_latin.pattern,split_table_latin.match)returns (res varchar(100) character set latin)) as t1;

Returns a table with the following rows

• The
• quick
• brown
• fox
• jumps
• over
• the
• lazy
• red
• dog
• .
  
  This all about the Regular Expression Functions. I hope you enjoyed while learning.
  
  
  
  

Continue Reading...

HASH Functions

There are four types of hashing functions are available in Teradata.

1. HASHROW      
2. HASHAMP
3. HASHBUCKET   
4. HASHBAKAMP

HASHROW Function 
Basic Synatx for HASHROW is
HASHROW( [ < data-column-value > […, < data-column-value >…] ] )

Example

SELECT    HASHROW (NULL)     AS NULL_HASH
                 ,HASHROW('Anil')        AS Name_HASH
                 ,HASHROW()                 AS NO_HASH ;

• The Query will give same results if you ran again and again.
• The HASHROW function produces the 32-bit binary Row Hash that is stored as part of the data row.
• It returns maximum of 4,294,967,295 unique values.
• The values range from 0 to FFFFFFFF.

General example is
SELECT HASHROW(Emp_no) AS HASHROW_VALUE FROM Emp_table;

Result:
HASHROW_VALUE
------------
717A8724

HASHBUCKET Function

Basic Syntax for HASHBUCKET is

HASHBUCKET( [ ] )

Example
SELECT HASHBUCKET(NULL) AS NULL_BUCKET
                ,HASHBUCKET()    AS NO_BUCKET;

• The HASHBUCKET function produces 16bit binary Hash Bucket  that is used with the Hash Map to determine the AMP that should store and retrieve the data row
• The values range from 0 to 1,048,575,not counting the NULL as a potential result.

General Example
SELECT COUNT(*) AS NBR_ROWS
               ,HASHBUCKET(HASHROW (Emp_No)) AS Bucket_No
               FROM Emp_Table
                GROUP BY 2 ;

NBR_ROWS	Bucket_No
1	388963
1	1560021
1	683238
1	53025
1	1877998
1	795490
1	410221
1	1048185
1	804626
1	317398

HASHAMP Function
Basic Synatx for HASHAMP is
HASHAMP( )

Example
SELECT HASHAMP(NULL) AS NULL_BUCKET
                ,HASHAMP ()    AS NO_BUCKET;

• The HASHAMP function returns the identification number of the primary AMP for any Hash Bucket number.
• When no value is passed to the HASHAMP function, it returns a number that is one less than the number of AMPs in the current system configuration

 General Example

SELECT Emp_no   
       ,HASHBUCKET(HASHROW(Emp_no)) AS Bucket_No
       ,HASHAMP(HASHBUCKET(HASHROW(Emp_no))) AS AMP_No
       FROM Emp_Table
       ORDER BY 1;

Emp_no	Bucket_No	AMP_No
1777	973955	120
4972	93325	41
7524	540321	56
21048	957958	69
21048	957958	69
24402	707988	65
26346	9707	71
27470	471190	15
29157	613408	25
29775	452863	120

HASHBAKAMP Function
Basic Syntax for HASHBAKAMP is 

HASHBAKAMP ( )

ExampleSELECT HASHBAKAMP (NULL) AS NULL_BUCKET
               ,HASHBAKAMP () AS NO_BUCKET;

The HASHBAKAMP function returns the identification number of the Fallback AMP for any Hash Bucket number

General Example
SELECT Emp_no    
       ,HASHBUCKET(HASHROW(Emp_no)) AS Bucket_No
       ,HASHBAKAMP(HASHAMP(HASHBUCKET(HASHROW(Emp_no)))) AS BAK_AMP_No
       FROM Emp_Table
       ORDER BY 1;

Emp_no	Bucket_No	BAK_AMP_No
2226	439931	88
6467	977645	94
24115	222558	92
24402	707988	94
25316	277218	94
25499	290771	95
29405	528628	93
40115	944038	95
40117	892602	86
40564	410014	85

Continue Reading...

CUBE Function

I hope you all familiar with ROLLUP Function now. If not please read before get into CUBE Function.

• The CUBE function can be called as Superset of RollUP function.
• Because It returns the same result set as ROLLUP along with provides some additional information.
• Instead producing a 'hierarchy' of results, CUBE looks at all permutations of the specified dimensions.

Let take an example , Produce a 'CUBE' report showing employee salaries across the dimensions of department and manager.

SELECT DEPT_NO AS DEPTNO
 ,MGR_EMP_NO AS MANAGER
 ,SUM(SALARY_AMOUNT) AS SUMSAL
 FROM EMPLOYEE
 WHERE DEPT_NO < 402
GROUP BY CUBE (DEPT_NO,MGR_EMP_NO)
 ORDER BY 1,2;

DEPTNO	MANAGER	SUMSAL
?	?	591925	(all depts, all managers)
?	801	286750	(all depts, one manager)
?	1003	207725
?	1019	58700
?	1025	38750
100	?	100000	(one dept, all managers)
100	801	100000	(one dept, one manager)
201	?	73450	(one dept, all managers)
201	801	34700
201	1025	38750
301	?	116400
301	801	57700
301	1019	58700
302	?	56500
302	801	56500
401	?	245575
401	801	37850
401	1003	207725

If you observe there are four additional rows (green) are included in the output.
These four rows represent the totals by manager.This report shows all possible permutations of the two specified dimensions.

 Reversing CUBE Parameters:
 What happens when we flip the sequence of the CUBE parameters? Consider the following:

 SELECT DEPT_NO AS DEPTNO
 ,MGR_EMP_NO AS MANAGER
 ,SUM(SALARY_AMOUNT) AS SUMSAL
 FROM EMPLOYEE
 WHERE DEPT_NO < 402
GROUP BY CUBE (MGR_EMP_NO,DEPT_NO)
 ORDER BY 1,2;

Yes, The output is exactly  returns same.There is no difference in output becacuse CUBE function looks at all permutations of the specified parameters, order of the arguments doesn't matter - unlike ROLLUP where it does matter.

 we can use the GROUPING and COALESCE functions to make the report more readable.

 SELECT CASE GROUPING (department_number)
 WHEN 1 THEN 'All Depts'
 ELSE (COALESCE (department_number,'Null Dept') ) END AS Deptno
 ,CASE GROUPING (manager_employee_number)
 WHEN 1 THEN 'All Managers'
 ELSE manager_employee_number END AS Mngr
 ,SUM(salary_amount) AS SumSal
 FROM employee
 WHERE department_number < 402
 GROUP BY CUBE (manager_employee_number,department_number)
 ORDER BY 1,2;
 

Deptno	Mngr	SumSal
100	801	100000
100	All Managers	100000
201	801	34700
201	1025	38750
201	All Managers	73450
301	801	57700
301	1019	58700
301	All Managers	116400
302	801	56500
302	All Managers	56500
401	801	37850
401	1003	207725
401	All Managers	245575
All Depts	801	286750
All Depts	1003	207725
All Depts	1019	58700
All Depts	1025	38750
All Depts	All Managers	591925

As more dimensions are added to the cube, more permutations and thus more rows will result.

 If n is the number of dimensions in a cube, then 2**n represents the number of permutations generated:
 2 dimensions - 4 permutations
 3 dimensions - 8 permutations
 4 dimensions - 16 permutations
 etc.
Example

Produce a 'cube' report showing employee salaries across the three dimensions of department, manager and job code for department numbers less than 302.


SELECT CASE GROUPING (department_number)
 WHEN 1 THEN 'All Depts'
 ELSE (COALESCE (department_number,'Null Dept') ) END AS Deptno

,CASE GROUPING (manager_employee_number)
 WHEN 1 THEN 'All Managers'
 ELSE manager_employee_number END AS Mngr

 ,CASE GROUPING (job_code)
 WHEN 1 THEN 'All Job Codes'
 ELSE (COALESCE (job_code,'Null Job Code') ) END AS Job

,SUM(salary_amount) AS SumSal
 FROM employee
 WHERE department_number < 302
GROUP BY CUBE (manager_employee_number,department_number, job_code)
 ORDER BY 1,2;

Deptno	Mngr	Job	SumSal
100	801	111100	100000
100	801	All Job Codes	100000
100	All Managers	All Job Codes	100000
100	All Managers	111100	100000
201	801	211100	34700
201	801	All Job Codes	34700
201	1025	All Job Codes	38750
201	1025	222101	38750
201	All Managers	211100	34700
201	All Managers	All Job Codes	73450
201	All Managers	222101	38750
301	801	All Job Codes	57700
301	801	311100	57700
301	1019	All Job Codes	58700
301	1019	312101	29450
301	1019	312102	29250
301	All Managers	312102	29250
301	All Managers	311100	57700
301	All Managers	All Job Codes	116400
301	All Managers	312101	29450
All Depts	801	All Job Codes	192400
All Depts	801	111100	100000
All Depts	801	211100	34700
All Depts	801	311100	57700
All Depts	1019	312101	29450
All Depts	1019	All Job Codes	58700
All Depts	1019	312102	29250
All Depts	1025	All Job Codes	38750
All Depts	1025	222101	38750
All Depts	All Managers	All Job Codes	289850
All Depts	All Managers	311100	57700
All Depts	All Managers	211100	34700
All Depts	All Managers	111100	100000
All Depts	All Managers	312101	29450
All Depts	All Managers	312102	29250
All Depts	All Managers	222101	38750
All Depts	All Managers	312102	29250
All Depts	All Managers	222101	38750

To help interpret the output, use the following color-coding scheme:

(department,manager, job_code)       Each unique combo of dept/mgr/job
(department, manager)                        Each unique combo of dept/mgr
(department, job_code)                        Each unique combo of dept/job
(manager, job_code)                             Each unique combo of mgr/job
(department)                                         All depts
(manager)                                             All mgrs
(job_code)                                             All jobs
( )                                                           Grand Total

Continue Reading...

ROLLUP Function

The teradata ROLLUP function is used, when ever an aggregation is required across all levels of a hierarchy within a single dimension.

Lets take geography is a dimension. You might have 3 levels of hierarchy - city, county and state. You may wish to produce sum of revenue amounts across all three levels.

we can produce below samples 

• Produce a sum of revenue amounts across all three levels.
• Aggregate at the lowest level (city) and 'rollup' those aggregations to the next level (county) and finally rollup to the top level (state).
• Provide a grand total across all three levels.

All of these are easily done with the ROLLUP feature.

Example1:
Using the GROUP BY function, produce a total of salaries by department for department numbers less than 402.

SELECT department_number
       ,SUM(salary_amount)
FROM employee WHERE department_number < 402
GROUP BY 1
ORDER BY 1;

department_number	Group Sum(salary_amount)
100	100000
201	73450
301	116400
302	56500
401	245575

ROLLUP Function - One Level
Using the ROLLUP function, produce salary totals by department.

SELECT department_number
,SUM(salary_amount)
FROM employee
WHERE department_number < 402
GROUP BY ROLLUP (department_number)
ORDER BY 1;

department_number	Group Sum(salary_amount)
?	591925
100	100000
201	73450
301	116400
302	56500
401	245575

There is an additional row with a '?' department number. This row represents the rollup of all department salaries which is the top (and only) level of the hierarchy in this case. The '?' does not represent a null, rather it represents the 'total' of all department salaries.

ROLLUP Function - Two Levels

Produce a hierarchy of total salaries by department within manager.

SELECT manager_employee_number AS Mgr
,department_number AS Dept
,SUM(salary_amount) AS SumSal
FROM employee
WHERE department_number < 402
GROUP BY ROLLUP (manager_employee_number,department_number)
ORDER BY 1,2;

Mgr	Dept	SumSal
?	?	591925	(all mgrs,all depts,all emps)
801	?	286750	(mgr 801,all depts, 5 emps)
801	100	100000	(mgr 801,dept 100, 1 emp)
801	201	34700	(mgr 801,dept 201, 1 emp)
801	301	57700	(mgr 801,dept 301, 1 emp)
801	302	56500	(mgr 801,dept 302, 1 emp)
801	401	37850	(mgr 801,dept 401, 1 emp)
1003	?	207725	(mgr 1003,all depts, 6 emps)
1003	401	207725	(mgr 1003,dept 401, 6 emps)
1019	?	58700	(mgr 1019,all depts, 2 emps)
1019	301	58700	(mgr 1019,dept 301, 2 emps)
1025	?	38750	(mgr 1025,all depts, 1 emp)
1025	201	38750	(mgr 1025,dept 201, 1 emp)

Three separate aggregations taking place in the preceding query.

Totals for each dept/manager combo (black).
This is represented as the set '(manager,department )' and is the bottom of the hierarchy.

Totals for each manager across all departments (red).
This is represented as the set '(manager )' which is the middle of the hierarchy.

A total for all depts across all managers (blue).
This is represented as the set '( )' and is the top of the hierarchy.

ROLLUP WITH NULL
As previously indicated, the row with the '?' represents the grand total of all salaries across all departments.
But what if there was a department number with a null? How would we distinguish between it and a 'total' row?

Add an employee with a null department and a null job code:

INSERT INTO employee VALUES
(1050,801,NULL,NULL,'LaCoste','Jason',780415,480816,60000.00);

SELECT department_number
,SUM(salary_amount)
FROM employee
GROUP BY ROLLUP (department_number)
ORDER BY 1;

department_number	Group Sum(salary_amount)
?	651925
?	60000
100	100000
201	73450
301	116400
302	56500
401	245575

While it may seem obvious that the row with the larger sum will represent the 'total' row,
a function exists which permits the report to be more easily read.

Continue Reading...