4.5.1 Creating a New Query
To create a new query for a relational database, follow the steps below:
To create a new query by modifying an existing query, see Creating a Derived Query.
1. Open the Style Studio Asset panel, and expand the 'Data Source' node.
2. Right-click on the data source that you wish to query, and select 'New Query' from the context menu. This opens the 'Query Wizard' dialog box.

3. Under the General tab of the 'Query Wizard' dialog box, enter a name for the query and (optional) description. Click 'Next' to advance to the next tab.
You can also double-click fields to add them, or use the arrow buttons.
4. Under the Data tab, drag the desired database tables from the 'Data Sources' panel to the 'Tables' panel. Click 'Next' to advance to the next tab.

To manually enter SQL rather than using the Wizard, click the SQL String tab. See Editing a Query SQL String.
5. Under the Links tab, define a set of joins to relate the tables. This process is the same as defining joins for physical views. (See Defining Join Relationships for an explanation of how to relate tables.) Click 'Next' to advance to the next tab.

You can also double-click fields to add them, or use the arrow buttons.
6. On the Fields tab, drag the fields that you wish the query to return from the 'Database Fields' panel to the 'Query Fields' panel.

7. (Optional) To create a derived (expression) column, follow the steps below:

a. Click the 'Add Expression' button above the 'Query Fields' panel. This opens the 'Field Edit' dialog box.
Double-click the field names to add them to the expression.
b. Enter an expression to define the new column, then click 'OK'. (The expression should use the appropriate SQL syntax for the underlying database.)

c. To create an alias for the expression column, select the expression column in the 'Query Fields' panel, and type a name into the 'Field Alias' box in the bottom panel.

d. Click 'Next' to advance to the next tab.
8. On the Conditions tab, use the menus to specify a filtering condition. This condition will restrict the data returned by the query. Click the “arrow” button on the right side to select a method of supplying the right-hand side of the condition.

When you enter a fixed value as the right-side of the condition, use the 'Browse Data' button to assist the selection.
The 'Field' option allows you to select a different column from the same result set. 'Expression' allows you to enter a regular expression using Perl5 regex syntax (see Appendix A.5, Regular Expressions.) 'Value' allows you to enter a fixed value. 'Subquery' allows you to specify a distinct query to return the right-side of the condition. 'Variable' allows you to enter an arbitrary variable name. The value of the variable will either be provided by the user, by script, or by another subquery. (See Query Properties for more details about variables.)
9. Click 'Next' to proceed to the Sort tab. Drag the fields on which you wish to sort from the 'Available Fields' panel to the 'Sort Fields' panel.
An arrow to the left of the field name indicates the current sort order.
10. Select a field in the 'Sort Fields' panel, and click the 'Order' button above the panel. Click once to sort in descending order. Click again to sort in ascending order.

The table below summarizes the operation of the different tabs in the 'Query Wizard' dialog box. The same tabs are available also when editing a query. See Editing a Query for more details.
Table 2. Query Definition Tabs
Tab Name |
Description |
Links |
Shows all tables selected in the query and their relationships (joins). Joins are created automatically if the data model contains join information. Right-click a join line to change the join properties. |
Fields |
Column selection and column alias definition. Data formatting and auto-drilldown options are the same as for logical models. See Adding a Format to a Data Field and Auto-Drilldown in the Logical Model section for more information. The 'Retrieve distinct rows only' option prevents the query from returning duplicate rows. |
Conditions |
Specifies query selection conditions. |
Sort |
Sorting order of the table. |
Grouping |
Grouping columns and 'Having' conditions. The having conditions can only be defined if grouping is defined. |
SQL String |
The resulting SQL string or directly-entered SQL statement. The 'Parse SQL' checkbox controls whether the statement is parsed by Style Studio into a structured query. For very complicated SQL statements that are slow to parse, you may wish to disable parsing. In this case, you should click 'Get Column Info' to get the meta-data by executing the query. Click it again whenever the meta-data (columns or data-types) change. |
| << 4.5 Independent Query | © 1996-2013 InetSoft Technology Corporation (v11.5) | 4.5.2 Creating a Derived Query >> |