Using the Query Builder

You use the Query Builder to define the SQL statement that retrieves the result set used by a report. The following image shows the parts of the Query Builder interface:

You use these parts, called panes, as follows:

Database pane Displays a list of data sources for an RDS server. Expand the data sources to display a list of tables in the related database. To use a table, double-click it or drag it to the Table pane. If you have selected a data source, you can also select a table by right-clicking on the Table pane.

Table pane Displays columns in the tables you selected. Double-click a column to add it to the Properties pane and to the SELECT statement that is displayed in the SQL pane.

Note: The Table pane can only browse data. You cannot modify data or add new records here. Also, views are available only in databases supporting the creation of views or tables stored as queries.

Properties pane Displays selected columns. Use this pane to specify additional criteria, such as ALIAS names, sorting, sort order, and selection criteria for the WHERE clause.

SQL pane Displays the SQL statement as it is being constructed. The SQL pane does not support reverse editing, so any changes that you make in this pane are not made in the Properties pane or the Table pane.

Query type Controls whether you create a SQL statement through the Query Builder or by creating a query using CFML (also known as advanced query mode). To create a query using CFML, click Advanced.

The Query Builder uses RDS to access database schema information through the data sources defined to a ColdFusion MX server. For more information, see Configuring RDS.

The Query Builder automatically defines query fields for the columns returned by your SQL statement. Because the Query Builder executes the SQL statement and analyzes the columns that it returns, automatic query field definition applies to both stored procedures and SELECT statements.

Note: The Query Builder does not create query fields automatically for queries generated using advanced query mode.

Not all DBMSs support all SQL syntax. Ensure that your DBMS supports the SQL statements generated by the Query Builder. For example, the Microsoft Access and Microsoft Access with Unicode drivers do not support FULL OUTER JOIN clauses.

To select an RDS server:

• Select the RDS server from the drop-down list in the upper left corner of the Query Builder.

Caution: The Query Builder clears the current SELECT statement when you select a new RDS server.

You can define RDS settings for multiple ColdFusion servers. For more information, see Configuring RDS.

To select a data source in the current RDS server:

• Select the data source name in the Data Source Used by the Query drop-down list at the upper edge of the Query Builder.

Caution: The Query Builder clears the current SQL statement when you select a new data source,

For more information on creating SQL statements with the Query Builder, see "Using the Query Builder" in Report Builder online Help.

To include cfquery attributes in the SQL statement:

1. Use the Query Builder to create a SQL statement.
2. Click the <cfquery> Attributes button.

   The CF Query Options dialog box appears.

3. Specify cfquery attributes through dialog box fields.
4. Click OK.

View comments in LiveDocs