Performing Delete Operations Using a SQL Transformation Project

Create a sample SQL Transformation project to delete data out of a table in Sybase IQ.

1. From the Navigator, right-click SQL Transformation Projects and select New > SQL Transformation Project.
2. Specify the database connection parameters. If you have a previously defined connection profile for the database connection, select it from the Profile list. This list displays the Sybase and ODBC profiles stored in the same repository as the project in the Database Configuration window and the Property window.
3. Drag the Task Group component onto the Design window and select Generic SQL as the task type. Enter a name and description for the task and click Create.
4. If necessary, click the output port of the Start component and drag it onto the input port of the Task Group component to create a connection. This connection is made automatically if the Create links automatically when components are added preference is selected.
5. Double-click the Task Group component to open the Task Container editor.
6. Select the task you created and click the Edit the Selected Task icon to open the Generic Transformation editor, where you can define the transformation rules. Before opening the Generic Transformation editor select one of these:
   • Critical – specify each task as critical or noncritical.
   • Single transaction – execute all tasks in a group as a single transaction.
   • Continue on error – continue processing even if an error occurs.
7. On the Target tab, select DELETE as the transformation operation.
8. Drag a destination table from the Model tab to the Destination text field. For example, DEMOUSER. SALESDETAILS.
   When you select the transformation operation and the destination table, a SQL statement is generated automatically and appears in the Transformation pane under the Generated Transformation tab.
9. Add a WHERE clause to this transformation.

   • Click the Tables tab under the Transformation Definition pane.

   • Select a table from the Model tab and drag it onto the Design window. For example, DEMOUSER. SALESDETAILS.

   • Click the source_table_name tab under the Transformation pane and select the attributes to add to the generated transformation. For example, under the DEMOUSER. SALESDETAILS tab, select the WHERE option to filter or add a WHERE clause on the Quantity attribute.

   • Go to the Filters tab under the Transformation Definition pane and enter a filter condition. For example, to delete records from the DEMOUSER. SALESDETAILS table with a quantity less than or equal to 500, enter <= as the Operator and 500 as the Value.
10. Click the Generated Transformation tab to view the regenerated SQL statement. In this case, the generated transformation is updated with the WHERE clause. Click Save to return to the project window.
11. Drag the Decision component onto the Design window. Create a link between the Task Group component and Decision component.
12. Drag the Finish component onto the Design window. Connect the input port of this component to the Success output port of the Decision component.
13. Drag the Error component onto the Design window. Connect the input port of this component to the Error output port of the Decision component.
14. Save the project. Click Execute on the toolbar to run the project.
    Click Close after the project successfully executes.
    You can view and edit the project generated SQL script by clicking the SQL Script icon in the Properties window. However, if you regenerate the script, you lose the changes you have added in.
    Note: SQL Transformation projects also support key based delete whereby target table data may be deleted if the attributes selected as keys in the Target table match the attributes mapped in the source query. To work properly, you must add attributes to the Attributes tab of the source query in proper order.