This section uses the Demo Getting Started project from the Demo Repository to create queries.
To generate a simple query that retrieves all attributes from a table, use the PRODUCTS table.
In the Navigator, select the Model tab, then click the table or view name. To search for a particular table or view name, press Ctrl+F.
Drag the selected object to the Design window.
To view the results of the generated query, select View | Generated Query or select the Generated Query tab.
Creating a query using multiple tables
To generate a query that joins and retrieves information from two tables, use the PRODUCTS and SALES tables.
Drag the PRODUCTS table from the Navigator to the Design window.
Drag the SALES table from the Navigator to the Design window.
Create a join between the tables by linking the PR_ID fields of both tables. If you want Query Designer to automatically create joins between identically named attributes within tables or views:
Select File | Preferences from the main Sybase ETL Development window.
Select Workbench | Query Designer, then select Create joins automatically. See “Customizing preferences”.
To view join attribute details, click the Join tab in the Query Designer window.
Modifying the default settings of a join
A join between two tables is indicated by a line that connects the joining fields. The line is labeled with a join operator, which, by default, is called Equi Join.
Right-click the line connecting the two joining fields.
Select Modify.
Select a join type.
Modifying
the sort order of joins
In the Join tab, right-click a row and select:
Move to start
Move up
Move down
Move to end
To revert to the default state of the join at any point, right-click a row and select Sort joins to default order.
Adding one or more attributes to the select clause
Drag the PRODUCTS and SALES tables to the Design window, if they are not there already.
To add a single attribute, right-click the attribute you want to add, and select Add Items to Selection. To add more than one attribute, hold down the Ctrl key while you select the attributes you want to add.
Alternatively, click the PRODUCTS and SALES tabs in the Query Definition pane and select the attributes you want to add to the select clause. To search for an attribute, click the Search icon and provide your search criteria.
You can use an asterisk (*) as a wildcard to search for any number of unknown characters. For example:
If your attribute is an integer datatype, your search criteria can be one of these:
int, int*, i*ger
If your attribute name contains “PROD” and has “CD” at the end, your search criteria can be:
*PROD*CD
Adding all attributes of a selected table to the
select clause
In the Query tab, click the header of the table.
Right-click and select Add Items to Select.
Viewing attribute details and generated query
To view a query generated by the Query Designer, select View | Generated Query, or select the Generated Query tab.
Click the appropriate tabs to view the attribute details.
Adding functions to the select attribute
In the Select tab, right-click the attribute to which you want to add functions.
Choose the functions to add.
To enforce appropriate and reliable attribute names
for viewing data and port structures, define alias names for all
attributes to which functions have been applied. See Adding alias names to selected attributes and “Use column aliases when entering queries”.
Adding alias names to
selected attributes
In the Select tab, enter a name in the Alias column to enforce an output column name used in Data Viewer and associated port structure.
