Switch offers the Database variable group which allows users to select any value from a database and use it like any other variable to:
- Use values for the tools and the configurator properties
- Use it as values for the conditions. Example: filtering on a connection
- Use it as values to fill a drop-down menu in the Submit point and Checkpoint metadata entry
Before querying a database, user should first create an SQL statement. The Build SQL
statement dialog box assists user in creating the SELECT statement and obtaining
values form the database. The dialog box helps to:
- define the variable
- create the statement
Database Group
The Database group is available in the Define Text with Variables dialog box through which Switch offers a library of variables.Using the Define Text with Variables dialog box, user
can:
- Select the Database group, select the type of Database variable
- Modify text
- Build SQL statement
|
Variable name |
Data type |
|---|---|
|
Database.Text |
Text |
|
Database.TextIndexed |
Text Indexed |
|
Database.Boolean |
Boolean |
|
Database.Integer |
Integer |
|
Database.Rational |
Rational |
| Database.Date | Date |
Build SQL statement
Click the Build SQL statement option in the fly-out menu which appears when you click this
button
beside SQL textbox in the above dialog box to invoke the Build
SQL statement dialog box.
The above dialog box helps you to,
- Select a data source. Click the Configure button to open the ODBC data sources dialog box using which you can select a data source from the list of predefined data sources (refer ODBC data sources for more information on how to define the data sources).
- Select a table and a column in the Table and the Column drop-down menus.
- Select the Condition checkbox and specify or select the conditions in the first and the third menus, choose the comparison operator in the middle drop-down menu and choose the AND or OR logical operator in the last drop-down menu.
- In order to choose more than one column or to choose a column from any other table or not to choose any columns, select the Manual editing checkbox and enter the statement manually.
- After setting the SQL statement, select a job and click the Validate statement button to check if the query is valid. If the query returns any value or error, it is displayed to the user.
- The Sample value menu displays the sample value for a query. If a query returns multiple values, they will be listed in a comma separated list. For example: "val11,val12,val21,val22...".
- Click the OK button to insert the statement.