Copyright: Borland SQL Builder Copyright (c) 1997 Borland International, Inc.


SQL Builder overview

SQL Builder enables you to visually and interactively create and execute SQL queries. An expert knowledge of SQL isn’t necessary to create complex SQL queries. SQL Builder can even be a tool for learning SQL.

When you use SQL 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 SQL Builder user interface is composed of the following elements:

Table pane

Query notebook

SQL Query Text Entry window

Query Results window

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 and editing the query as text

Viewing query results


Heterogeneous queries

SQL 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.


SQL Query Text Entry window

The SQL Query Text Entry window enables you to view and edit the SQL query as text. As you build your query graphically in SQL Builder, the query appears as a SQL statement in this window. You can toggle between the text entry window and SQL Builder at will. Changes that you make here will be reflected in SQL Builder graphically when you toggle back to SQL Builder.

To edit the current SQL query,

Click on the [image] icon in the SQL Builder toolbar.

When you use SQL 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. SQL 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. Anything after the ‘—‘ is a comment.

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

If an attempt to save a syntactically incorrect or non-ANSI-92 SQL syntax, a dialog is displayed with the following choices:

Edit the current statement	Allows you to edit and correct the current SQL statement.
Revert to the last valid statement	Replaces the erroneous SQL statement with the most recent correct SQL statement.
Menu commands

Toolbar commands


Query Results window

The Query Results window appears when you execute the SQL query. The grid, that you can scroll using the browse buttons above the grid, contains results from the query.

To view query results

Click on the [image] icon in the SQL Builder toolbar.

Menu commands

Toolbar commands


SQL Builder toolbar

The SQL Builder toolbar appears on the main SQL 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 SQL Builder window.

Button	Command
New	Create a new SQL query.
Open	Open an existing SQL query.
Save	Save the current SQL query.
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.
View SQL query	(SQL Builder window only) Show and edit the SQL query as text in the SQL Query Text Entry window.
Show as Graphical	(SQL Query Text Entry window only) Show and edit the SQL query graphically in SQL Builder window.
Run SQL query	Execute the SQL query.
List box	Meaning
Database	(SQL Builder window only) Select an available database from a list of aliases.
Table	(SQL Builder window only) Select a table from the database to add to the query.


Table Pane

Adding tables	Selecting fields	Creating joins	Heterogeneous queries

The Table Pane is a scrollable, sizable window that displays the tables that have been added to the pane.

Tables

Each table appears as a scrolling, sizable, collapsible window 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 a 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 on joins, see Creating joins.


Adding tables

Selecting fields	Creating joins	Heterogeneous queries

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.

Adding a table

1. Select the database containing the table from the alias drop-down 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 drop-down list box on the toolbar.

2. Select the appropriate table from the Table drop-down list box on the toolbar. The table will then appear in the table pane.

Renaming a table

1. Right-click on the table window and select Rename Table.

2. Type an alias for the table name in the edit box.

Removing a table

1. Right-click on the table window and select Remove Table.

2. A table may also be removed by pressing the Delete button when the table window has focus.


Selecting fields

Selection page	Adding tables	Heterogeneous queries

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, click on the table name’s check box to add a black check mark. A black check mark indicates that all fields are selected. Uncheck 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 automatically adds them to the Selection page.

Selecting individual fields in a table

Check each field you wish to appear in the result set. When a field is checked and unchecked, it is automatically added to and removed from the Selection page.

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

Reordering selected fields

In the left gray column of the grid, drag the row and drop it at the 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

Adding selection criteria	Combining selection criteria	Grouping selection criteria

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.

Deleting 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

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 in 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. Enter 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 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. Enter 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 box, 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 box. 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 box, or entering a constant value or valid SQL expression into the Field or Values column.

SQL Expression

Enter 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 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 enter the statement following the ‘EXISTS’ into the SQL Expression column.

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


Combining selection criteria

Grouping selection criteria	Adding selection criteria	Criteria page

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 the 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

Combining selection criteria	Adding selection criteria	Criteria page

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 the CTRL key and clicking each row you want to select in the drill down column. The drill down column is located in the leftmost column of the grid and doesn’t have a header description.

2. Right-click in the drill down column and choose Group Rows from the context menu.

For example, to group the city and company criteria into a nested OR statement from the following rows, hold down the CTRL key, and then click the city and company rows in the drill down column. Right-click in the grid and choose Group rows from the context menu.

After grouping city and company criteria:

Drill down column

The drill down column is the leftmost column of the grid and doesn’t have a header description. 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.

To ungroup 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

Adding selection criteria	Goup criteria page

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

Table Pane

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.

Selecting a field

Select a field from the Field drop-down 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 and dropped onto the Field column.

Specifying an output name

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

Producing summary data

Right-click in the grid and select Summary from the context menu. The grid will have three columns: Output Name, Summary, and Field. Select the appropriate function from the Summary column’s drop-down list box. You can also drag a field from a table window in the table pane and drop it onto the Field column.

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

Removing duplicate rows

When the Remove Duplicates box is checked every row in the query results will be unique. Checking this box adds the DISTINCT keyword to the SQL statement.

Deleting a row

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


Grouping page

Goup criteria 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. Select 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 have a field appear in the Output Fields list box, select the field in the Table Pane.

To remove a field from the Grouped On list box, select the field and click the Remove button.


Group criteria page

The Group Criteria page enables you to specify group selection criteria used in the HAVING clause that SQL 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 in the grid and select the appropriate criteria type from the context menu.

Adding group selection criteria

To change the type of selection criteria, right-click in the grid on the Group Criteria page and select one of the following from the grid’s context menu.

SQL Expression

Enter 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.

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

To enter a Simple Having Summary Expression:

1. Select the appropriate summary value from the Summary column.

2. Enter the first field you wish the summary to compare. This can be done by either dragging a field from a table window in the table pane and dropping it onto the Field column or selecting a field from the drop-down list box.

3. Select the appropriate operator from the Compare column drop-down list box.

4. Enter the second field you wish the summary to compare as described in step 2.

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. Select the appropriate summary value from the Summary column for the first summary to compare.

2. Enter the first field you wish to summarize for the comparison. This can be done by either dragging a field from a table window in the table pane and dropping it onto the Field column or selecting a field from the drop-down list box.

3. Select the appropriate operator from the Operator column drop-down list box. This operator defines the type of comparison between the two summaries.

4. Select the appropriate summary value from the Summary column for the second summary to compare.

5. Enter the second field you wish to summarize for the comparison as described in step 2.

Combining group criteria

To specify how the selection criteria are combined to form more complex selection criteria, select from the drop-down 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.
Deleting a row

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


Sorting page

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

Sorting query results

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

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

Toggling sort order

double click on the field in the Sorted by list box.  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.

Sorting on multiple fields

simply add more 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.

Adding a field to the Output Fields list box

select the field in the Table Pane.

Removing a field from the Sorted By list box

select the field and click the Remove button.

Reordering fields in the Sorted By list box

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

2. Click on the gray up arrow to left of the list box to move the field up in the sort order. Click on the gray up down arrow to left of the list box to move the field down in the sort order.


Joins page

Creating joins	Table Pane

The Joins page enables you to create multi-table SQL queries (joins). See Creating joins for steps on how to create a join.

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 doesn’t include an outer join in its specifications. Some SQL servers have restrictions on outer joins and some don’t allow outer joins. Please see your server documentation for information on 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 have a field appear as a choice in the Fields column drop-down 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 <>.

Deleting a join

To delete a join, in the table pane, right-click on the join line be the two joined tables.

Deleting a row

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


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, select the appropriate type of match. You may choose from =, <, >, <=, >=, and <>.

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


File menu commands

These commands appear on the File menu.

Command	Description

New	Creates a new query.

Open	Opens or finds a query file.

Save	Saves the current query text to a file.

Save As	Lets you specify a name for the query file to save.

Exit	Exits SQL Builder.


Edit menu commands

These commands appear on the Edit menu. Most of these commands are also available from the tool bar.

Command	Description

Cut	Removes the selection and places it on the clipboard.

Copy	Copies the selection and places it on the clipboard.

Paste	Copies the contents of the clipboard to the cursor position.


Query menu commands

These commands appear on the Query menu.

Command	Description

Run SQL F9	Execute the SQL query.

Show SQL F7	Show and edit the SQL query.


Help menu commands

Command	Description

Contents F1	Displays the WinHelp contents window.

About	Displays version and copyright information.