Query Builder Dialog Boxes

From DBArtisan
Jump to: navigation, search

Go Up to Query Builder Design

Query Builder includes a number of dialog boxes to assist you in building and customizing your query.

Dialog Box Description

Statement Properties

Specifies general properties in an individual Query Builder session.

Table Properties

Specifies column selection and alias names for a table or view.

Column Properties

Specifies column functionality within SELECT and CREATE VIEW statements.

Statement Properties

The Statement Properties dialog box lets you customize properties in an individual Query Builder session. For example, you can set options to limit the number of rows returned in a query to save execution time, or turn off the auto join function to manually control all joins for an individual query. These properties override the global options set in the Options editor for the current session. For details, see Query Builder Options.

The table below describes the options and functionality of the Statement Properties dialog box.

Interface Element Option Description Default

Code Generation

Generate Use Database statement

Adds a line of SQL code indicating which database or instance is used in the statement.

Selected

Generate owner names

Adds a line of SQL code showing the table owner name as part of the query.

Selected

Include Row Count limits

Includes the output row limit set in the Execution settings.

Selected

Execution

Max Row Count in Results Set

Sets row count limits to build and check a query without congesting server processes when a query executes.

1000 rows

General

Show Column Data types in Query Diagram

Lets Query Builder reveal the data type in each column for tables in the SQL Diagram Pane.

Not selected

Confirm on Item delete

Lets Query Builder open a Confirm Delete dialog box when an item is deleted. NOTE: Clearing this function can result in unexpected changes to your query diagram and statement.

Selected

Auto populate views

Lets Query Builder automatically populate views.

Not selected

Auto Join

Require Indexes

Joins indexed columns automatically, and requires indexed columns for joins.

Selected

Require same data type

Automatically joins columns with the same data type.

Selected

Syntax Checker

Automatic Syntax Check

Lets Query Builder check syntax every time an execute statement, refresh or copy statement begins.

Selected

Run Automatically

Lets Query Builder automatically detect like names and data types and create joins for multiple tables.

Selected

Display

Columns Font

Lets you set the font, font style, size, and color of column fonts.

Available

Title Font

Lets you set the font, font style, size, and color of table/view title fonts.

Available

Table Color

Lets you set the background color of your tables in the SQL Diagram Pane.

Available

Note: If you set options in the Options editor while Query Builder is running, a warning indicates that you are about to change options or properties. For details, see Query Builder Options.

Completing the Statement Properties Dialog Box

To complete the Statement Properties dialog box, do the following:

  1. On the Query Builder menu, click Statement Properties.
  2. Set options.
  3. Click OK.
    Query Builder saves the options.

Table Properties

The Tables Properties dialog box lets you set parameters for tables or views in your SQL Diagram. The table below describes the options and functionality on the Table Properties dialog box.

Option Description

Table Alias

Creates an alias name for your table.

Show Datatypes

Shows or hides the datatype for every column in the target table.

Displayed Columns

Displays columns visible in the SQL Diagram.

Hidden Columns

Displays columns hidden in the SQL Diagram.

Hide All

Moves all non selected columns in the table to the Hidden Columns window.

Display All

Moves all columns in the table to the Displayed Columns window.

Right Arrow

Moves a target file from Displayed Columns to Hidden Columns.

Left Arrow

Moves a target file from Hidden Columns to Displayed Columns.

Completing the Table Properties Dialog Box

To complete the Table Properties dialog box, do the following:

  1. Double click the target table or view title bar.
    OR
    Right-click target table or view, and then click Properties.
  2. If you only want to hide or display columns in your table, click the arrow button on the table title bar.
  3. You can also edit view properties from the Table Properties dialog box.
  4. Click OK.
    Query Builder saves the changes.

Column Properties

The Column Properties dialog box lets you set properties for individual columns in your SELECT or CREATE VIEW statements. You can set aggregate functions and create an alias for an individual column.

The Column Properties dialog box is not available for INSERT, UPDATE or DELETE statements.The table below describes the options and functions Columns Properties dialog box:

Interface Element Description

Tables/Views

Displays all tables and views in the SQL Diagram Pane.

Aggregate

Specifies aggregate options for the target column. AVG - An average is taken for a column with an int or numeric datatype. COUNT - Returns the number of rows which contain data for the target column. MAX - Returns the highest number in a row in the column. MIN - Returns the lowest number in a row in the column. SUM - Returns the sum of the target column in all rows which contain data. This function is only operable on int or numeric datatypes.

Alias

Displays the alias name for the target column. Lets you type the name of the alias. NOTE: Query Builder displays the results of an aggregate column without a column name unless you create an alias for that column.

Available Columns

Displays all available columns in the target table or view.

Selected Columns

Displays all selected columns in the target table or view. To create an aggregate function or alias for a different column, select target column, select an aggregate function, and then type the name of the alias.

Select All

Moves all columns in the Available Columns box to the Selected Columns box.

Clear All

Moves all columns in the Selected Columns box to the Available Columns box.

Right Arrow

Moves target column in the Available Columns box to the Selected Columns box.

Left Arrow

Moves target column in the Selected Columns box to the Available Columns box.

Select List Statement

Displays the current query.

Completing the Column Properties Dialog Box

To complete the Column Properties dialog box, do the following:

  1. On the SQL Statement Tree, double-click target column.
  2. Select options.
  3. Click OK.

Join Properties

Query Builder lets you edit joins with the Join editor. You can edit join parameters in a SELECT, UPDATE, DELETE, and CREATE VIEW Statement.

The table below describes the options and functionality on the Join dialog box.

Option Description

From Table Column

The primary column in the join.

To Table Column

The secondary column in the join.

Select the join relation operator

Click the target join operator. If it is not equals, the operator displays on the join in the SQL Diagram Pane.

Join Type: Inner

Click to make the join an inner join. Aggregates are only available for inner joins.

Join Type: Left Outer

Click to make the join a left outer join.

Join Type: Right Outer

Click to make the join a right outer join.

Note: For IBM DB2 for Linux, Unix, and Windows and IBM DB2 for z/OS and OS/390 servers, there is an additional join object in the SQL Statement Tree. The Join On node displays join relations between columns in IBM DB2 for Linux, Unix, and Windows and IBM DB2 for z/OS and OS/390 tables and views.

Completing the Join Dialog Box

To complete the Join dialog box, do the following:

  1. In the SQL Diagram Pane, right-click the target join, and then click Properties.
    OR
    In the SQL Diagram Pane, double-click the target join.
    OR
    On the SQL Statement Tree, expand the Where and And nodes, and then double-click the target join.
  2. Select options.
  3. Click OK.

Topics