Query Designer

Copyright © Cloud Software Group, Inc. All Rights Reserved

Query Designer

Wizard

The Query Designer is a GUI tool for building SQL queries, testing syntax, and previewing results before running the process. You can use the Query Designer in one of the following ways:

•

to automatically generate SQL queries

•

to manually edit and check the syntax of existing queries

In the JDBC Query activity, you must first specify a JDBC Connection shared configuration resource to connect to a database. Once the database connection is specified in the JDBC Query activity, you can click the Build Using Wizard button to create a query using the Query Designer wizard. Figure 18 illustrates the Query Designer.

Figure 18 The Query Designer wizard

The Query Designer window has the following elements:

•

a Database Schema panel, where schema information for the specified database and user is displayed in a tree structure.

•

a Table Diagram panel, where you can drag and drop tables from the Schema panel to build a SQL query.

•

a SQL panel, where you can design, refine, view, and test the query. This panel has three tabs for designing, editing, and testing your queries.

Prepared parameters in BusinessWorks are inserted into a query by using the question mark (?) character. This is not valid SQL syntax, but BusinessWorks uses this syntax for inserting process data into the query at runtime. Because this is not valid SQL syntax, an error is returned if you attempt to check the syntax of a query that has prepared parameters.

You should design and test the syntax of your query before inserting prepared parameters.

You can use the Design tab of the Query Designer to automatically generate queries. You can then click on the SQL tab of the Query Designer to manually edit the query, if you want. To manually edit the query, click the Customize check box on the SQL tab. On the SQL tab, you can drag and drop columns into the statement.

After you customize a query on the SQL tab, returning to the design tab without checking the Customize check box eliminates your changes. First, create a query using the Design, then click the Customize check box on the SQL tab to further customize it as needed.

To use the Query Designer to generate a query, perform this procedure:

1.

Drag tables to include in the query from the Database Schema panel to the Table Diagram panel. Any joins defined between primary keys in the database are displayed.

For example:

This example demonstrates building a query that retrieves names of customers that also have placed orders. After dragging the CUSTOMER and SALES_ORDER tables from the Database Schema panel to the Table Diagram panel, a join is automatically displayed between the CUSTOMER_ID column in each table. You can also manually add joins by dragging the cursor from one join column to the other.

Clicking the SQL tab in the SQL panel shows the automatically generated syntax:

   SELECT *

   FROM DEMO.CUSTOMER, DEMO.SALES_ORDER

   WHERE (DEMO.CUSTOMER.CUSTOMER_ID = DEMO.SALES_ORDER.CUSTOMER_ID)

By default, all table columns are included in the query. To constrain query syntax, click the Design tab in the SQL panel. You can drag individual columns from the Table Diagram panel to the Add Column area of this tab to refine the query. The Add Column entry is a placeholder only, and does not affect the output. It is meant to be the area where you can drag columns from the tables to add to the query.

The following example shows the results of dragging the CUSTOMER_ID, NAME, TOTAL, and SALESPERSON_ID columns to the SQL tab:

Clicking the SQL tab to view the modified syntax shows the query now selects only from the set of columns:

   SELECT DEMO.CUSTOMER.CUSTOMER_ID, DEMO.CUSTOMER.NAME,       DEMO.CUSTOMER.SALESPERSON_ID, DEMO.SALES_ORDER.TOTAL

      FROM DEMO.CUSTOMER, DEMO.SALES_ORDER

      WHERE (DEMO.CUSTOMER.CUSTOMER_ID = DEMO.SALES_ORDER.CUSTOMER_ID)

2.

To group sales by customer and show sales totals, perform the following:

−

For the TOTAL column, click in the Total field and select Sum from the list. The other columns are automatically updated to select the Group By value required for this function.

−

For the CUSTOMER_ID column, click the Show check box to clear it. This column is necessary for the query, but can be hidden in the output.

Clicking the SQL tab to view the syntax shows the SUM function and GROUP BY clause has been added to the SQL:

   SELECT DEMO.CUSTOMER.NAME, DEMO.CUSTOMER.SALESPERSON_ID,       SUM(DEMO.SALES_ORDER.TOTAL)

      FROM DEMO.CUSTOMER, DEMO.SALES_ORDER

      WHERE (DEMO.CUSTOMER.CUSTOMER_ID =

      GROUP BY DEMO.CUSTOMER.CUSTOMER_ID, DEMO.CUSTOMER.NAME,       DEMO.CUSTOMER.SALESPERSON_ID

Other criteria can be applied using the Sort, Criteria, and Or fields.

3.

To check the SQL syntax, click the Check Syntax button.

A dialog displays either a success message, or a database error code and message.

4.

After checking the syntax, click the Execute button on the Test tab to preview the query results:

A representative number of rows, specified by the Maximum Rows value, is returned.

5.

When the query returns the desired result set, click OK.

The SQL syntax is automatically entered in the SQL Statement field of the Database task.

The following usage restrictions apply to the Query Designer:

•

You can build, modify, and test SQL queries only, not UPDATE statements or stored procedures.

•

Only queries that are automatically generated can be manipulated and regenerated using the Design tab features. Query syntax that was manually typed in the SQL Statement field can be modified by typing or dropping column names on the SQL tab. The syntax checking and preview results features can also be used.

•

After manually editing an automatically generated query on the SQL tab, your changes are lost if you uncheck the Customize box on the SQL tab and return to the Design tab.

•

If you want to select columns with the same name from different tables, you must specify column aliases for the columns that have the same name. In SQL, you may select columns with the same name from different tables in the same SQL statement, however, TIBCO ActiveMatrix BusinessWorks requires that all names in an input or output schema are unique. Therefore, you must specify column aliases to ensure that all items in the output schema for a query are unique.