Relational Queries

After you have set up the relational data sources, you can add queries to the data.

Add a Relational Data Query

To add a new relational data query, you must have already created a data source that points to the relational database and specifies the database type.

1. In CDM, do either of the following to open the Data Queries tab in the work area:
   • Click the File tab, then navigate to Administration > External Data > Data Queries.
   • Click the Home tab, then in the Navigation Pane, click Administration and double-click Data Queries under External Data.
2. Click Home > Add > Relational Query, or right-click any existing query in the query list and click Add > Relational Query. The Add Relational Query Wizard displays.
3. Enter a query name in the Query Name field.
4. Select a relational data source from the Connection Name drop-down list.
5. Optionally, specify a description for the query.
6. Click Next.
7. Select the checkboxes in front of the reports that you want to associate with this query or select Make This Query Global to make the query available to all reports.
8. Click Finish. The Query Builder displays for you to build the SQl query for retrieving data from the specified relational data source.

Build a Relational Data Query

CDM provides a database querying module that is integrated with Active Query Builder to cope with SQL query building.

When you query relational data sources in CDM, you must retrieve data through SELECT statements. If you use SQL statements other than SELECT, such as UPDATE or DELETE, they are reported as errors.

See SQL Queries for information such as the SQL keywords and operators you can use in CDM.

To build a relation data query:

1. In the Data Queries tab in the work area, double-click a relational database query in the query list.
2. The Query Builder gives you a choice of methods for creating SQL queries. You can type SQL commands directly in the SQL Query with Variables pane of the window, or you can drag tables into the Main pane and create queries using a graphical interface. The following steps show how to use the graphical interface.
3. Drag a table from the pane on the right that displays database tables into the Main pane and select columns from the table. The column is displayed in the Expression drop-down list.
4. Specify the query parameters to customize the query:
   • Aggregate - Select an aggregate from the drop-down list.
   • Alias- Type an alias if needed.
   • Sort Type - Select a sort type from the drop-down list.
   • Sort Order - Select a sort order from the drop-down list.
   • Grouping - Select which items to group.
   • Criteria - Select criteria that the query must meet.
5. Use the following commands in the Query Builder tab to work with the query:
   • Refresh Metadata - Refreshes the current query metadata.
   • Insert Query Variable - Adds a variable to the query.
   • Validate - Validates the query syntax.
   • Run - Executes the query.
   • Stop - Stops query execution.
   • Show Structure - Toggles the Query Structure pane.

For detailed instructions about how to build SQL query using the Query Builder, refer to the Active Query Builder User's Guide.