Working with Operations
Operations provide the main processing functionality to transform your data. This section includes the following topics:
Designing a Query Operation
The Query editor that can be accessed through the Transformation Editor is similar in behavior to the Studio View editor. Queries allow data to be joined, sorted, grouped, filtered, and projected.
SQL functions can be used in the Query operation if the query gets pushed to a SQL database for execution. Outside of the Query operation you can use canonical, XQuery or custom functions.
To transform your data using query
2. From the Transformation Editor palette, select Query.
3. Click and drag Query anywhere in the Model tab.
4. Double-click the Query operation to obtain a Query editor.
— On the Join tab of the editor, you can click and drag resources from the Studio resource tree. You can also draw relationship lines between the rows of the resources that have been added. For more information on similar functionality, see the View editor documentation in the TDV User’s Guide.
— On the Grid tab you can:
— Specify columns for projection.
— Move the columns up or down to determine their output order.
— Supply aliases for column names.
— Sort columns in ascending or descending order.
— Specify the GROUP BY option.
— Specify query constraints in the WHERE or HAVING clause of the query.
— Add functions and declare variables for the query.
5. Save your transform.
Adding Cast Operations from the Palette
You can use the CAST function converts a value from one data type to another.
A cast operation is always required when performing a narrowing conversion, such as converting from an xs:string to a VARCHAR.
To transform your data using cast
2. From the Transformation Editor palette, select Cast.
3. Click and drag the mouse anywhere in the Model tab.
4. Click to select the Cast operation that you just added.
5. Draw a link from the column that you want to transform with the cast function, to the CAST operation.
6. Save your transform.
7. Double-click the Cast operation.
8. Select the data type that you want your data to become.
9. Specify any additional data type details.
10. Click OK.
11. Connect the result side of the Cast operation with the column where the data will be consumed next.
12. Save your transform.
The following model diagram shows a transform using Cast operations:
Adding Union Operations
Union functionality provides the ability to combine data into one unified data set. You can specify whether or not to filter out duplicates.
The sources for a union must have exactly the same structure (element names and types) in order for them to be combined using a union operation. The two different spellings of "flag" and "falg" will produce an error. You can create a child transformation to act as an intermediate value or as a structure cast.
To transform your data using union
2. From the Transformation Editor palette, select Union.
3. Click and drag the mouse anywhere in the Model tab.
4. Connect at least two parameters from somewhere else in your transform to the Union operation. The two parameters must be of the same data type.
5. Connect the Union operation’s result to another operation within your transformation.
6. (Optional) Add more source parameters by:
a. Click the Union operation to select it.
b. Right-click and select Add Source from the menu.
7. (Optional) Select Distinct, to indicate that you only want unique values in the outcome of the Union operation.
8. Save your transform.
The following model diagram shows a simple transform using the Union operation:
Adding Switch Operations
You can use the Switch operation to conditionally control the flow of data from one or more sources. Each source has a corresponding case expression. At runtime, the case expressions are evaluated from top to bottom. The first case expression to evaluate to true causes the corresponding source to be output from the switch.
To transform your data using switch
2. From the Transformation Editor palette, select Switch.
3. Click and drag the mouse anywhere in the Model tab.
4. Connect at least one parameter from somewhere else in your transform to the Switch operation.
5. Connect the Switch operation’s results to other operations within your transformation.
6. (Optional) Add more source parameters by:
a. Click the Switch operation to select it.
b. Right-click and select Add Source or Add Parameter Insert from the menu.
7. (Optional) Rename source or result parameters by:
a. Select the operational handle of the parameter you want to rename.
b. Right-click and select Rename “<parm_name>”.
Or, click Rename from the Transformation Editor Model tab toolbar.
c. Type the new name.
8. Define the conditions on the switch. Each condition is called a Case. Each Case is evaluated in the order specified and the first case to evaluate to true is returned in the result.
— To add new Case expressions, click the green plus button.
— To edit the expression defined for each case, click in the text edit fields and edit the text as necessary to define your expression.
— To adjust the order that the conditions are evaluated, use the arrow buttons to move the row up or down.
— To close and save your definitions, click Close.
If your expression contains illegal characters you must enclose them in double quotes. The following characters are considered illegal:
'\\', '[', ']', '-', '(', ')', '.','^','$','+', '*','?', '{', '}', '|', ':', '<', '>', '<', '='
9. Save your transform.
The following model diagram shows a transform using the Switch operation:
Using the Create Operation Button
To use the Create Operation button
1. Open a transform.
2. Click Create Operation.
The last operation that was selected or is currently selected on the palette is added to the transform workspace.
3. Save your transform.
