Copyright: Corel Paradox Visual Query Builder Copyright (c) 1997 Corel Corporation
Citation: Corel Paradox Visual Query Builder Copyright (c) 1997 Corel Corporation


Visual Query Builder overview

The Corel Paradox Visual Query Builder enables you to visually and interactively create and execute SQL queries. An expert knowledge of SQL is not necessary to create complex SQL queries. The Visual Query Builder can even be a tool for learning SQL.

When you use the Visual Query Builder, you can view and edit the resulting SQL. You can create a full range of SQL queries, from the simplest SELECT to a complicated multi-table join with calculated fields and expression based joins.

The Visual Query Builder user interface is composed of the following elements:

Table pane

Query notebook

These are the tasks you can perform with SQL Builder:

Adding tables

Selecting fields

Adding selection criteria

Creating a grouped query

Sorting query results

Creating joins

Viewing the Visual Query Builder query in the SQL Editor

Running a Visual Query Builder query


Heterogeneous queries

The Visual Query Builder supports heterogeneous queries, queries made against tables in more than one database. A heterogeneous query may join tables on different servers, and even different types of servers. For example, a heterogeneous query might involve a table in a Oracle database, a table in a Sybase database, and a local dBASE table.

When you execute a heterogeneous query, the BDE parses and processes the query using Local SQL, so extended, server-specific SQL syntax is not supported. Local SQL is a subset of ANSI-standard SQL enhanced to support Paradox and dBASE naming conventions for tables and fields.


Viewing the Visual Query Builder query in the SQL Editor

As you build your query graphically in the Visual Query Builder, the query appears as a SQL statement in the SQL Editor. By positioning your windows side by side, you can view your query as you build it.

When you use the Visual Query Builder to create a query, the query is initially handled in ANSI-92 SQL syntax, then translated (if necessary) into the dialect used by the server. The Visual Query Builder reads only ANSI standard SQL; the dialect translation, which is updated automatically, appears after the ANSI version in the text entry window with a `-` at the beginning of each line indicating that it is a dialect SQL statement and anything after the `-` is a comment.

Queries graphically created with the Visual Query Builder can be extended with dialect specific constructs (non-ANSI-92 syntax). If you want to modify a query to be read back into the Visual Query Builder, edit the ANSI SQL.

Toolbar commands


Running a Visual Query Builder query

When you run a query that you have created using the Visual Query Builder, Corel Paradox displays the results in an Answer table, just as it does when you query your database using Query By Example (QBE).

To run a Visual Query Builder query

•	Click the [image] button in the Visual Query Builder Toolbar.

Toolbar commands


SQL Builder toolbar
Visual Query Builder Toolbar

The Visual Query Builder Toolbar appears on the main Visual Query Builder window and the SQL Query Text Entry window. It contains the following buttons and list boxes for executing commands. The list boxes appear only in the Visual Query Builder window.

Button	Command
Cut	Remove the selection and place it on the clipboard.
Copy	Copy the selection and place it on the clipboard.
Paste	Paste the contents of the clipboard at the cursor position.
Run SQL query	Execute the SQL query.
Help	Open Visual Query Builder Help file
List box	Meaning
Database	(Visual Query Builder window only) Select an available database from a list of aliases.
Table	(Visual Query Builder window only) Select a table from the database to add to the query.


Table Pane

The Table Pane is a scrollable, sizable window that displays each table.

Tables

Each table appears as a scrolling, resizable, collapsible window that containing a header with the table name and a list box containing all of the table's fields.

Next to each field and table name is a check box. If a table name has a dark blue check mark, all fields from that table will be in the query. Otherwise, only fields that are checked will be in the query. If at least one, but not all, fields are checked, the table name will have a light gray check mark.

Dragging the mouse over a table window displays fly-over help hint with the full name of the table. For example, the full name for a customer table might be ":mydb:Customer.dbf".

To view tables in a collapsed mode where only the table name appears, click on the minimize button next to the table name.

Tables can be joined by dragging a field from one table window to a field in another table window. When two tables are joined, a join line appears that links the joined tables. For more information about joins, see Creating joins.

To view a table in collapsed mode

•	Click the minimize button next to the table name.

Adding tables

Selecting fields


Adding tables

Tables are added to a query by simply adding them to the table pane. In the table pane you can select some or all fields of one or more tables to be included in the result set. You can also graphically join one table to another. A table may be added more than once.

To add a table

1.	Select the database containing the table from the Alias list box on the Toolbar. If the database is password protected, a dialog will appear asking you to enter a user name and password. Selecting a database adds all of its tables to the Table list box on the Toolbar.

2.	Select the appropriate table from the Table list box on the Toolbar. The table will then appear in the table pane.

To rename a table

•	Right-click on the table window and click Edit Table Alias.

An edit box appears allowing you to type an alias for the table name.

To remove a table

•	Right-click on the table window and click Remove Table.

A table may also be removed by pressing the Delete button when the table window is the active window.


Selecting fields

Each table window in the table pane has a table and field name with a check box that allows you to select some or all fields to be included in the result set.

Selecting all fields in a table

In a table window, enable the check box beside a table name. A black check mark indicates that all fields are selected. Disable this box to remove all fields from the query. If the check box has a gray check mark, only a portion of the fields have been selected to appear in the result set.

The F6 key and SPACEBAR also enable you to toggle field selections.

Selecting all fields this way automatically adds them to the Selection page.

Selecting individual fields in a table

Enable each field you wish to appear in the result set. When a field is enabled and disabled, it is automatically added to and removed from the Selection page.

Fields can also be selected by dragging them from the table window to the Selection page grid.

Reordering selected fields

In the left gray column of the grid, drag the row to a new location.


Query notebook

The Query notebook is a tabbed notebook of query parameter grids that allow you to specify different options for the query. Each page has options that will affect the query in a particular way.

Criteria page

Selection page

Grouping page

Group Criteria page

Sorting page

Joins page


Criteria page

The Criteria page allows you to specify selection criteria that the query will use to include only certain rows of data in the query results. Adding selection criteria to this page adds a WHERE clause to the query. The criteria can be either a simple expression, an SQL expression, or an EXISTS clause.

The grid contains the selection criteria by which the query will exclude rows of data.

The drop-down list box specifies whether ALL, ANY, NONE, or NOT ALL of the criteria apply.

To delete a row

•	After selecting the row to delete, right-click and choose Delete Row from the context menu.

Adding selection criteria

Combining selection criteria

Grouping selection criteria


Adding selection criteria

Selection criteria in a query specifies which rows of data are included in the query results. You enter selection criteria into the Criteria page of the Query notebook.

Specifying selection criteria

1.	In the Criteria page grid, choose the type of criteria you wish by right-clicking the grid and selecting the appropriate criteria from the grid's context menu. You may choose from Simple Equation, SQL Expression, and EXISTS. Each type is described below.

2.	Type criteria into the row according to the type of criteria you have chosen.

Simple equation

A simple equation compares the values of two values for each row of data. For example:

CustNo >= 1000

The values can be either a field name, constant value or any valid SQL expression. String and date constant values must be surrounded by single or double quotes.

When defining a simple equation the grid has three columns: Field or Value, Compare, and Field or Value.

To enter a Simple equation

1.	Type the first field or value you wish to compare into the first Field or Value column. This can be done by either dragging a field from a table window in the table pane and dropping it onto the Field or Value column, selecting a field from the drop-down list, or entering a constant value or valid SQL expression into the Field or Values column.

2.	Select the appropriate comparison operator from the Compare column drop down list. You can choose from =, >, <, >=, <=, <>, LIKE, IN, BETWEEN, NOT BETWEEN, IS NULL, or IS NOT NULL.

3.	Enter the field or value you wish to compare to the first into the second Field or Value column. This can be done by either dragging a field from a table window in the table pane and dropping it onto the Field or Value column, selecting a field from the drop down list, or entering a constant value or valid SQL expression into the Field or Values column.

SQL Expression

•	Type a SQL expression directly into the SQL Expression column. For example:

((CustNo < 2000) OR (CustNo > 3000))

String and date constant values must be surrounded by single or double quotes.

EXISTS Clause

Adding an EXISTS clause returns True when the subquery produces at least one row of query results.

When a row to has this type of selection criteria, the row in the grid has two columns: Operator and SQL Expression. Select EXISTS from the Operator column. You can now enter a SQL expression to see if any rows are produced.

The following example returns all the companies who have placed orders:

SELECT Company FROM Customer.db WHERE EXISTS (SELECT * FROM Orders WHERE Orders.CustNo = Customer.CustNo)

In the preceding example, you would type the statement following the `EXISTS' into the SQL Expression column.

String and date constant values must be surrounded by single or double quotes.


Combining selection criteria

Row info

When Row Info is enabled, a NOT, OR and AND are displayed to the left of the grid next to a row and indicates the rules for combining the criteria rows.

To enable row info

•	Right-click on grid and select Row Info from the grid's context menu.

Criteria combo box

•	To specify how selection criteria rows are combined to form more complex selection criteria, select from the Criteria combo box above the criteria grid.

ALL	Specifies that all selection criteria in the grid must be true for the combined criteria to be true. With row info enabled an AND will appear next to each additional row after the first row.
ANY	Specifies that at least one of the selection criteria in the grid must be true for the combined criteria to be true. With row info enabled an OR will appear next to each additional row after the first row.
NONE	Specifies that all of the selection criteria in the grid must be false for the combined criteria to be true. With row info enabled a NOT will appear next to the first row and an AND will appear next to each additional row after the first row.
NOT ALL	Specifies that at least on of the selection criteria in the grid must be false for the combined criteria to be true. With row info enabled a NOT will appear next to the first row and an OR will appear next to each additional row after the first row.


Grouping selection criteria

Individual selection criteria can be grouped together to form nested selection criteria.

To group selection criteria

1.	Select the rows to group by holding down CTRL and clicking each row you want to select in the Drill Down column. The drill down column is located in the leftmost gray column of the grid.

2.	Right-click the Drill Down column and click Group Rows.

For example, to group the city and company criteria into a nested OR statement from the following rows, hold down CTRL, and click the City and Company rows in the Drill Down column. Right-click the grid and click Group rows.

After grouping city and company criteria:

Drill down column

The Drill Down column is the leftmost gray column of the grid. If the query contains a nested selection criteria, a `drill down' arrow appears next to the nested row. Clicking this arrow `drills down' into the expression. If a grouped expression has already been drilled down, a `drill out' arrow appears in upper left cell of the grid. Clicking this arrow `drills out' the expression.

CTRL+U and CTRL+D also allow you to drill up and drill down respectively.

Ungrouping selection criteria

Change the operator in the grouped expression to its opposite.  For example, if you have the previous grouped expression:

(City = `Freeport') OR (Company = `Unisco')

Change the OR to an AND:

(City = `Freeport') AND (Company = `Unisco')


Operators

Operator	Description
>, <, >=, <=, <>	Standard boolean operators for a comparison test.
LIKE	Adds a LIKE clause to the query. Tests whether the data matches the specified pattern.
NOT LIKE	Adds a NOT LIKE clause to the query. Tests whether the data value does not match the specified pattern.
IN	Adds an IN clause to the query. Tests whether the data matches at least one value in the list of values. To create this list of values, enter fields and/or values separated by commas into the second Field or Value column.
NOT IN	Adds a NOT IN clause to the query. Tests whether the data does not match any value in the list of values. To create this list of values, enter fields and/or values separated by commas into the second Field or Value column.
BETWEEN	Adds a BETWEEN clause to the query. A BETWEEN clause tests whether the field or value falls within a specified range of values. For example, you can use this to return the salespeople whose sales are between $50,000 and $200,000.
NOT BETWEEN	Adds a NOT BETWEEN clause to the query. A NOT BETWEEN clause tests whether the field or value is outside a specified range of values.
IS NULL	Adds an IS NULL clause to the query. Tests whether the field or value contains a NULL value.
IS NOT NULL	Adds an IS NOT NULL clause to the query. Tests whether the field or value does not contain a NULL value.


Selection page

The Selection page enables you to create summary data. It also allows you to specify a customized output name for a field or summary data in the query results.

To select a field

•	Select a field from the Field list box. Fields will be available for each table that  appears in the table pane. A Field may also be dragged from a table window in the table pane to the Field column.

To specify an output name

•	In the Output Name column for a field or summary, type the name you wish to appear as the title for that field or summary data rather than the default.

To produce summary data

•	Right-click the grid and click Summary. The grid will have three columns: Output Name, Summary, and Field. Choose a function from the Summary column list box. You can also drag a field from a table window in the table pane to the Field column.

When you add a summary, Visual Query Builder automatically groups on all of the non-summary fields to satisfy SQL syntax requirements.

Removing duplicate rows

When the Remove Duplicates box is enabled, every row in the query results will be unique. Enable this box to add the DISTINCT keyword to the SQL statement.

To delete a row

•	After selecting the row to delete, right-click and click Delete Row.


Grouping page

The Grouping page enables you to create a grouped query. A grouped query groups the data from the source tables and produces a single summary row for each row group.

Creating a grouped query

1.	Choose the field or fields you wish to group by from the Output Fields list box.

2.	Click the Add button to move the field to the Grouped On list box. The query will be grouped based on fields that appear in the Grouped On list box. To remove a field from the Grouped On list box, select the field and click the Remove button.

3.	To have a field appear in the Output Fields list box, choose the field in the Table Pane.

Group criteria page


Group criteria page

The Group Criteria page enables you to specify group selection criteria used in the HAVING clause that Visual Query Builder adds to the query. A HAVING clause selects and rejects row groups.

The group criteria can be a simple expression, a SQL expression or a two summary expression. Right-click the grid and click a criteria type.

To change the type of selection criteria

1.	Right-click in the grid on the Group Criteria page and click one of the following:

•	SQL Expression

Type a SQL expression directly. For example:

SUM (Qty * Price) > 1000

•	Simple Having Summary Expression

A Simple Having Summary Expression summarizes the comparison of two fields for each row of data.

When defining a Simple Having Summary Expression the grid has four columns: Summary, Field, Operator, and Field.

You can drag a field from a table window in the table pane to a Field column.

To create a Simple Having Summary Expression:

1.	Select a summary value from the Summary column.

2.	Type the first field you that you want the summary to compare. You can drag a field from a table window in the table pane to the Field column or choose a field from the list box.

3.	Choose an operator from the Compare column list box.

4.	Type the second field you wish the summary to compare. You can drag a field from a table window in the table pane to the Field column or select a field from the list box.

•	Two Summary Expression

A Two Summary Expression selects and rejects row groups based on the result of the comparison two summaries.

When defining a Two Summary Expression, the grid has five columns: Summary, Field, Operator, Summary, and Field.

A Field may be dragged from a table window in the table pane and dropped onto a Field column.

To enter a Two Summary Expression grouping criteria

1.	From the Summary column, choose the summary value that you want to compare to the first summary.

2.	Type the first field you wish to summarize for the comparison. You can drag a field from a table window in the table pane to the Field column or choose a field from the list box.

3.	Choose an operator from the Operator column list box. This operator defines the type of comparison between the two summaries.

4.	From the Summary column, choose the summary value for the second summary to compare.

5.	Type the second field you wish to summarize for the comparison. You can drag a field from a table window in the table to the Field column or select a field from the list box.

Combining group criteria

To specify how the selection criteria are combined to form more complex selection criteria, choose one of the following from the list box above the criteria grid.

ALL	Specifies that all selection criteria in the grid must be true for the combined criteria to be true
ANY	Specifies that at least one of the selection criteria in the grid must be true for the combined criteria to be true.
NONE	Specifies that all of the selection criteria in the grid must be false for the combined criteria to be true.
NOT ALL	Specifies that at least on of the selection criteria in the grid must be false for the combined criteria to be true.
To delete a row

•	After selecting the row to delete, right-click and click Delete Row.


Sorting page

The Sorting page enables you to specify a sort order for the query.

To sort query results

1.	Select the field you wish to sort by from the Output Fields list.

2.	Click the Add button to move the field to the Sorted By list.

To toggle sort order

•	Double click the field in the Sorted By list. To change sort order, you may also select the field and then to the left of the list box click on the A-Z for ascending order and Z-A for descending order.

To sort on more than one field

•	Add additional fields to the Sorted By list box. The query will be sorted based on order of the fields that appear in the Sort By list box.

To display a field in the Output Fields list

•	Select the field in the Table Pane.

To remove a field from the Sorted By list box

•	Select the field and click the Remove button.

To reorder fields in the Sorted By list box

1.	Select the field to move in the Sorted By list.

2.	Click the Up Arrow to left of the list to move the field up in the sort order. Click on the Down Arrow to left of the list box to move the field down in the sort order.


Joins page

The Joins page enables you to create multi-table SQL queries (joins). For more information see Creating joins.

Include Unmatched Records

These check boxes allow you to specify full, left, and right outer joins. If only the first check box is checked, a left outer join is added to the query. If only the second check box is checked, a right outer join is added to the query. If both check boxes are checked, a full outer join is added to the query. If neither box is checked (the default), an inner join is added to the query.

Note

•	The SQL1 standard does not include an outer join in its specifications. Some SQL servers have restrictions on outer joins and some do not allow outer joins at all. Please see your server documentation for information about its support for outer joins.

Join list box

This box appears above the grid and allows you to specify a particular join. When you select a join in this box, both tables in the join appear. When a join is selected, the grid contains the field information for that particular join.

Joins grid

The Joins grid contains three columns: Field, Operator and Field.

To include a field as a choice in the Fields column list boxes, select the field in the Table Pane.

The operator column allows you to specify a comparison operator for the join. You may choose from =, <, >, <=, >=, and <>.

To delete a join

•	In the table pane, right click the join line between the two joined tables.

To delete a row

•	After selecting the row to delete, right-click and click Delete Row.


Creating joins

Fields can be linked by dragging one or more fields from one table to the fields on another table. Graphically, a join is indicated by a single line that connects the two table windows at their table name.

Each linked field pair in the join is added as a separate row to the Joins page grid. When a join line is selected in the table pane, the join list box above the Join page grid will contain the two joined tables.

To create a join:

1.	In the first Field column, select the field you wish to match from the first table.

2.	In the Op column, choose a type of match. You may choose =, <, >, <=, >=, or <>.

3.	In the second Field column, select the field you wish to match from the second table.