In the SQL Statement tab, you can construct an SQL statement.
Note: For MS SQL Server, ensure that you make use of alias names for database functions in the SQL query to avoid runtime exception and suspension of the adapter workstep. For example, select UPPER(USER_NAME) AS USERNAME from UMUSER where USERNAME is the alias name for the database function, UPPER(USER_NAME).
To define SQL statements:
1. Choose the SQL Statement type from the following options: Select, Insert, Update, Delete, or Stored Procedure. These options are used to define an SQL Statement, and are described below:
Select. Enables you to select an existing record (or row) from a table to define a new SQL Statement.
Insert. Allows you to insert a record into a table.
Update. Enables you to update one or more selected records in a table.
Delete. Enables you to delete one or more selected records in a table.
Stored Procedure. Enables you to choose a procedure from ones you have defined previously and stored, and use it as a new SQL Statement.
2. Depending on the type you choose, one or more corresponding buttons are displayed in the Clauses pane.
3. Click Choose Tables. The list of Available Tables is displayed in the Choose Table(s) dialog box.
a. Choose the required tables. Based on the selection and the statement type, the configuration tool fills in one of the following into the statement:
Select from [tables]
Insert into [table]
Update [table]
Delete from [tables]
b. Click OK to return to the SQL Statement tab.
4. When your SQL statement type is Select and you have chosen a table, the Choose Columns button is enabled in the Clauses pane. Click Choose Columns to display the list of available columns for the selected table.
a. Choose the required columns for the syntax:
Select [columns] from [tables]
b. Click OK to return to the SQL Statement tab.
5. When your SQL statement type is Insert or Update and you select a Table, the Set Values button is enabled in the Clauses pane. Click Set Values to open the Select Columns & Set Their Values dialog box, which presents a list of Available Column.
a. Choose the required columns, enter its Value directly, or click in the Use Variable column to open the Variable Definitions dialog box, where you can take a value from an input variable. The configuration tool fills in the following syntax:
Insert into [table] (columns) (values)
Update [table] set [column1=value1, ...]
b. Click OK to return to the SQL Statement tab.
The input parameters for the SQL statements are usually provided through input or internal variables. The column value, however, may be a constant or another SQL statement.
6. When your SQL statement type is Select, Update, or Delete, the Add Conditions button is available in the Clauses pane. Click Add Conditions, to open the Compose a Condition Clause dialog box.
The condition clause is composed of expressions in the "column-operator-value" format.
a. Select a column from the Database Columns drop-down list.
b. Click Append a Column to append it to the condition clause.
c. Select an operator from the SQL Operators drop-down list.
d. Click Append an Operator to append it to the condition clause.
e. When you need to use an input variable, click Append a Variable to open the Variable Definitions dialog box, where you can select and append one of the listed variables. For more information on adding variables, see Step 3.
The SQL statement is displayed in the SQL Statement Preview pane.
f. Click OK to return to the SQL Statement tab.
7. If the above controls are not adequate for your SQL statement, you can select the Expert Mode button to directly modify the where clause. In this case, you need to be careful with the syntax, as SQL syntax parsing and verification is not carried out in the Expert mode. For more information, see Using theExpert mode.
8. When your SQL statement type is Select, you can click Get Result MetaData to open the Query Tester dialog box where you can verify your statements.
The upper Input Sample Values pane displays the parameters embedded in the SQL statement and the input variables to which they map.
a. Enter actual values for parameters in the Value column. Alternately, select the Use Default Values check box to take the parameter values from input variable default values.
b. Click Execute to test the query. The Query Result pane shows the test results.
c. Click OK to return to the SQL Statement tab.
When comparing ‘char’ database columns to variables, make sure that one of the following is true: 1) If an ‘equal to’ comparison is done and if the size in the variable is less than the size of the ‘char’ column, pad the variable value with spaces until the size of the ’char’ column is matched. Only then does the ‘equal to’ comparison work. 2) Use a database-specific trim function to trim the value selected from the ‘char’ column before running the comparison with a variable value. This results in trimming down additional spaces from the column value before comparison.
Select. Enables you to select an existing record (or row) from a table to define a new SQL Statement.