Example Using NORMALIZE
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 |