Developer Transformation Guide

10.5.2
- 10.5
- 10.4.1
- 10.4.0

Back Next

Manually Creating an SQL Transformation

You can manually create an SQL transformation. Manually create the transformation when you configure a transformation that runs an SQL query. You might also manually create a transformation that calls a stored procedure when the procedure is not available to import. When you manually create the transformation, you configure the input and output ports and type the SQL statements in the SQL Editor.

Select a project or a folder in the

Object Explorer

view.

Click

File

New

Transformation

The

New

dialog box appears.

Select the SQL transformation.

Click

Select

Create as Empty

Enter a name for the transformation and enter the repository location for the transformation.

Click

Finish

Click the

Overview

view to add ports to the transformation.

To add an input port, click

Input

in the

Ports

panel to indicate where to add the port. Click the

New

button and enter the port name, the native type, and the precision.

The default database type is Oracle. The Developer tool shows native types for Oracle databases unless you change the database type on the

Advanced

view.

To add an output port, click

Output

in the

Ports

panel before you add the port. Click the

New

button and enter the port name, the native type, and the precision.

The

SQLError

port is the first output port by default.

In the

Advanced

view, select the database type that the SQL transformation connects to. Configure other advanced properties for error handling and other optional properties.

When you choose the database type, the Developer tool changes the native datatypes of the ports on the

Overview

view.

Type the SQL query or stored procedure call on the

SQL

view. Select ports for parameter binding or string substitution in the

SQL Editor

If the stored procedure returns a result set, you must enter the stored procedure call with a syntax similar to the following syntax:

(?Field1?,?Field2?,?Field3?) = call
Stored_Procedure_Name (?Input_Parm?)

SQL Transformation

Define the SQL Query

Download Guide