NORMALIZE Option Examples | SQL SELECT Statements | Teradata Vantage - Examples: Using NORMALIZE - Advanced SQL Engine - Teradata Database

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qtb1554762060450.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

Following is the table definition for the NORMALIZE examples:

     CREATE TABLE project
     (    emp_id        INTEGER,
          project_name  VARCHAR(20),
          dept_id       INTEGER,
          duration      PERIOD(DATE)
     );

The table contains the following rows:

Emp_ID Project_Name Dept_ID Duration
10 First Phase 1000 10 Jan 2010 - 20 Mar 2010
10 First Phase 2000 20 Mar 2010 - 15 July 2010
10 Second Phase 2000 15 June 2010 - 18 Aug 2010
20 First Phase 2000 10 Mar 2010 - 20 July 2010

The following select statement performs a normalize operation on emp_id. Note that the select list contains only one period column.

     SELECT NORMALIZE ON MEETS OR OVERLAPS emp_id, duration
     FROM project;

The query returns the following result:

Emp_ID Duration
10 10 Jan 2010 - 18 Aug 2010
20 10 Mar 2010 - 20 July 2010

The following select statement performs a normalize operation on project_name. Note that the select list contains only one period column.

SELECT NORMALIZE project_name, duration
FROM project;

The query returns the following result:

Project_Name Duration
First Phase 10 Jan 2010 - 20 July 2010
Second Phase 15 June 2010 - 18 Aug 2010

The following select statement performs a normalize operation on project_name and dept_id. Note that the select list contains only one period column.

SELECT NORMALIZE project_name, dept_id, duration
FROM project;

The query returns the following result:

Project_Name Dept_ID Duration
First Phase 1000 10 Jan 2010 - 20 Mar 2010
First Phase 2000 20 Mar 2010 - 20 July 2010
Second Phase 2000 15 June 2010 - 18 Aug 2010