A Selector defines the data to retrieve when a query resource receives a request. You must add Selector to each query resource.
To add a Selector:
- In the Workbench Menu bar, choose Insert > Selector.
The Workbench adds a Selector to the resource.
- Drag the Selector into the Data Pipe and release. The Selector will be attached to the correct point automatically.
The Selector offers two options for defining selection criteria: the WHERE table and Manual SQL.
Using the WHERE Table to Select Data
To select data using the WHERE table:
- Click the Use WHERE table radio button. (NOTE: This is the default option).
- Click in the Variable column of the first empty row.
Percussion displays a popup menu with the options Function and Single Value.
- To enter a single value, choose Single Value. Percussion displays the Value Selector, which you can use to choose the value you want to enter. When entering values for this column of the selector, you will generally choose a Backend Column.
- To enter a database function, choose Function. Percussion displays the Function Properties dialog.
Select the function you want to use from the Select function drop list. If the function requires parameters, click the Value column for each parameter to display the Value Selector to enter the value for the parameter. The following functions are available:
- DAY
- CURRENT_DATE
- CURRENT_TIMESTAMP
- LEFT
- LOWER
- LTRIM
- MONTH
- RIGHT
- RTRIM
- SOUNDEX
- SUBSTRING
- TRIM
- UPPER
- YEAR
- IN-NUMBER
- IN-TEXT
- IN-NUMBER-ARRAY
- Click in the Op column and choose an operator for the row.
- Enter a Value to limit the data selected.
- If you selecting multiple columns, enter a Boolean operator in the Bool column. Options are AND and OR.
- To skip the row if the data in the specified database column is null, check the Omit if Null checkbox.
- To remove redundant rows from Selector results, check the Make entries distinct checkbox.
- Click the [OK] button to save your selection criteria.
Using Manual SQL to Select Data
In most cases, you should use the WHERE table to select your data. In cases when you have nested selection criteria or when you need to define complex joins that cannot be managed using the Join editor, you must use manual SQL.
If you use manual SQL, you must enter the complete SQL statement in the Selector; If you enter only the "where" clause, the selection will fail. Manual SQL does not rely on the Backend Data Tank to define the tables in the query or the Join editor to define the joins.
To use manual SQL to select data, on the Selector dialog, select the Manually enter SQL radio button. Enter your SQL statement in the SELECT statement field.