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 |