SQL Transformation Example with an SQL Query
You are a developer in the HR department of Hypostores corporation. Hypostores maintains employee payroll information in a separate database from the human resources employee data. The Human Resources department needs to query a single view of the employees and salaries across regions.
You want to create a logical data object mapping that shows a single view of the employee data and salary data in an employee logical data object.
Create a logical data object mapping with the employee data source. Include an SQL transformation to retrieve the salary and hire date from the payroll database.
Logical Data Object Mapping
The logical data object mapping contains the following objects:
- Employee table
- Input relational table of employee data from the Human Resources database.
- Salary table
- A table in the Payroll database that contains the employee salary and hire date. The database is an Oracle database.
- SQL transformation
- Transformation that retrieves the hire date and salary for each employee row. The transformation connects to a Payroll database and runs an SQL query against the Salary table in the database.
- Logical data object
- Contains the combined view of the employee and the salary data. The logical data object receives the output from the SQL transformation.
- SQLErrors file
- The SQLErrors file is flat file that contains any SQL error from the database. The Data Integration Service writes at least one row to the SQLErrors file for each input row. If no SQL errors occur, the SQLError column contains NULL. Review the SQLErrors file to troubleshoot errors.
Salary Table
The Salary table is a relational table in the Payroll database. The table contains employee data that the Payroll department maintains. The SQL transformation retrieves the hire date and the employee salary from the Salary table.
The following table shows some rows from the Salary table:
Employee_Num | HireDate | Salary |
|---|
10 | 3-May-97 | 232000 |
11 | 11-Sep-01 | 444000 |
12 | 17-Oct-89 | 656000 |
13 | 13-Aug-07 | 332100 |
Employee Table
The source is the Employee table from the Human Resources database.
The following table shows sample rows from the Employee table:
EmpID | LastName | FirstName | DeptId | Phone |
|---|
10 | Smith | Martha | FIN | (415) 552-1623 |
11 | Jones | Cynthia | ENG | (415) 552-1744 |
12 | Russell | Cissy | SLS | (415) 552-1656 |
13 | Goyal | Girish | FIN | (415) 552-1656 |
SQL Transformation
The SQL transformation retrieves the employee hire date and salary from the Salary table of the Payroll database. The Salary table is in an Oracle database.
Use the following steps to configure the SQL transformation:
- 1. Configure the SQL transformation properties.
- 2. Define the ports.
- 3. Create the SQL query.
- 4. Configure the database connection for the SQL transformation.
Define SQL Transformation Properties
Configure the SQL transformation properties in the Advanced Properties view.
Configure the following properties:
- Database type
- The database type is Oracle. When you define the ports, you can choose port datatypes that are applicable for Oracle.
- Continue on Error Within Row
- Disable. Stop processing if an SQL error occurs in the row.
- Include Statistics as Output
- Disable. Do not create the NumRowsAffected output port.
Define the Ports
Define input ports for each column in the employee source table. Select Copy to Output to change the input ports to pass-through ports for the columns. When you select Copy to Output, the Developer tool creates the corresponding output port for each port that you copy.
Create the following input pass-through ports:
Name | Type | Native Type | Precision | Scale | Copy to Output |
|---|
EmpID | decimal | number(p,2) | 4 | 0 | x |
LastName | string | varchar2 | 30 | 0 | x |
FirstName | string | varchar2 | 20 | 0 | x |
DeptID | string | varchar2 | 4 | 0 | x |
Phone | string | varchar2 | 16 | 0 | x |
The SQL transformation has the following output ports:
Name | Type | Native Type | Precision | Scale |
|---|
EmpID | decimal | number(p,s) | 4 | 0 |
LastName | string | varchar2 | 30 | 0 |
FirstName | string | varchar2 | 20 | 0 |
DeptID | string | varchar2 | 4 | 0 |
Phone | string | varchar2 | 16 | 0 |
HireDate | date/time | timestamp | 29 | 0 |
Salary | decimal | number(p,s) | 8 | 2 |
The Developer tool adds the "_output" suffix to each output port that it creates when you select Copy to Output.
Manually define the output ports for the hire date and salary columns. The SQL transformation returns the hire date and salary columns from the Salary table in the ports.
Define the SQL Query
Create an SQL query to select the hiredate and salary for each employee from the Salary table.
Define the query in the SQL transformation SQL view.
Enter the following query in the SQL Editor:
select HIREDATE,SALARY,from Salary where EMPLOYEE_NUM =?EmpID?
Hiredate, Salary, and Employee_Num are column names in the Salary table.
?EMPID? is a parameter that contains the value of the EmpID port.
Define the Database Connection
In the Runtime view, select a database connection object for the database that the SQL transformation connects to. Select an Oracle database connection object.
Output
Connect the SQLError port and the EmpID_output port to the SQLErrors flat file. The SQLError port contains null values unless an SQL error occurs.
Connect EmpID and the other output ports to the logical data object.
The SQL transformation returns a row that contains data from the Employee table and includes the hire date and salary from the Salary table.
The following table shows some rows from the logical data object:
EmpID | LastName | FirstName | DeptId | Phone | HireDate | Salary |
|---|
10 | Smith | Martha | FIN | (415) 552-1623 | 19970303 00:00:00 | 2320.00 |
11 | Jones | Cynthia | ENG | (415) 552-1744 | 20010911 00:00:00 | 4440.00 |
12 | Russell | Cissy | SLS | (415) 552-1656 | 19891017 00:00:00 | 6560.00 |
13 | Goyal | Girish | FIN | (415) 552-1660 | 20070813 00:00:00 | 3210.00 |