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

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

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