Appendix A – Integration Query Builder

The Integration Query Builder consists of following five panes:

  • The upper-right Connections pane, where you set up database connections
  • The lower-right DB Browser pane, which displays the tables and views available for use in the currently active database
  • The upper-left Relations pane, in which relationships between database objects are defined
  • The center-left Columns pane, in which you select fields and define criteria, as well as grouping and sorting
  • The lower-left SQL pane, in which the finalized SQL command is displayed and more complicated editing can be done

Connecting to the Database

  1. Select New Database Connection from the Database menu.
  2. Enter a unique name for this connection in the Connection Name box.
  3. Select the Data Access Type (see Table 1 below).
Table 1. Data Access Types
OLE DB Dynamic Data Access: ADO
If you select OLE DB Dynamic Data Access: ADO, the standard Windows Data Link Properties dialog box displays. Select the OLE DB provider you want to use from the list available. After selecting the appropriate OLE DB Provider, click the Next button to access the Connection tab to enter more details about the connection. The information required depends upon the provider selected. Click Test Connection to ensure you have entered the correct details.
OLE DB UDL link-file: ADO
If you already have the required connection information stored using a ".udl" file (Universal Data Link), select this type and navigate to the file using the Select Data Link File dialog box.
ODBC Machine Data Source: DSN or ODBC File Data Source: DAO
These ODBC data access options can be used when the data source does not support an adequate OLE DB Provider. They enable you to connect with data sources that have file data source names (DSNs).
In general, it is recommended that you use the previously described ADO options rather than these.

In all cases when you have specified a valid connection and entered the required information, a new connection node is added in the upper-right Connections pane and you can to click this in future to open this connection.

Integration Querybuilder SQL Functions

The following SQL functions are supported in the Integration Querybuilder:

Concatenate Functions

Highlight multiple rows in the Columns pane, and select the appropriate function from the menu. The selected fields are converted to string data type and concatenated as defined in the following table.>p>

Table 2. Concatenate and Formatting Descriptions
Fld1+", "+...+", "+FldN
Creates a string by concatenating all selected fields and inserting a comma followed by a space between them. For example: "Name1, Name2, Name3"
Fld1+","+...+","+FldN
Creates a string by concatenating all selected fields and inserting a comma between them. For example: "Name1,Name2,Name3"
Fld1+" "+...+" "+FldN
Creates a string by concatenating all selected fields and inserting a space between them. For example: "Name1 Name2 Name3"
Fld1+...+FldN
Creates a string by concatenating all selected fields without separating them. For example: "Name1Name2Name3"
Fld1+"-"+...+"-"+FldN
Creates a string by concatenating all selected fields and inserting a hyphen between them. For example: "Name1-Name2-Name3"
Fld1+" - "+...+" - "+FldN
Creates a string by concatenating all selected fields and inserting a space followed by a hyphen followed by space between them. For example: "Name1 - Name2 - Name3"
Fld1+", "+Fld2+" "+Fld3
Creates a string by concatenating the three selected files and inserting a comma and a space between the first and second fields and a space between the second and third. Used for last name, first name and middle name (or initial) format. For example: "Name1, Name2 Name3"
Fld1+" "+Fld2+". "+Fld3
Creates a string by concatenating the three selected files and inserting a space between the first and second fields and a period followed by a space between the second and third. Used for first name, middle initial, last name format. For example: "Name1 N. Name3"

Mask Functions

Highlight a row from the Columns pane and select the appropriate function from the menu. The selected field is converted to string data type and formatted as described below:

Table 3. Masks and Formatting Descriptions
Phone [LO] : 1-999-999-9999
Converts the first 11 characters to a string using this phone number format.
Phone [LO] : 1 999-999-9999
Converts the first 11 characters to a string using this phone number format.
Phone [AC] : (999) 999-9999
Converts the first 10 characters to a string using this phone number format.
Phone [AC] : 999-999-9999
Converts the first 10 characters to a string using this phone number format.
Phone [AC] : 999.999.9999
Converts the first 10 characters to a string using this phone number format.
Phone [ACX]: (999) 999-9999 (ext 00000)
Converts the first 15 characters to a string using this phone number plus extension format.
Phone [SH] : 999-9999
Converts the first 7 characters to a string using this phone number format.
Area Code : (999)
Converts the first 3 characters to a string using this area code format.
Extension : (ext 00000)
Converts the first 5 characters to a string using this extension number format.
Extension : -X00000
Converts the first 5 characters to a string using this extension number format.
SS# : 999-99-9999
Converts the first 10 characters to a string using this social security number format.
Fed ID : 99-9999999
Converts the first 9 characters to a string using this federal ID number format.
Zip Code : 99999-9999
Converts the first 9 characters to a string using this zip code format.
Bank Acc : 99999-99999
Converts the first 10 characters to a string using this bank account number format.
Cred Card : 9999-9999-9999-9999
Converts the first 16 characters to a string using this credit card number format.
ISBN : 9-99-999999-9
Converts the first 10 characters to a string using this ISBN format.
ISBN : 9-999-99999-9
Converts the first 10 characters to a string using this ISBN format.
ISBN : 9-9999-9999-9
Converts the first 10 characters to a string using this ISBN format.
ISBN : 9-99999-999-9
Converts the first 10 characters to a string using this ISBN format.
ISBN : 9-999999-99-9
Converts the first 10 characters to a string using this ISBN format.

Aggregate Functions

Aggregate functions return a single value based on a set of other values, such as the sum of these values, including the maximum and minimum. The aggregate function can be applied to all the values in a particular field in the query, or to groups of these values by applying the Group By clause.

The aggregation functions available depend on the SQL variant and syntax used by the data source. For a full description of each function, refer to the vendor's documentation.

Other than COUNT, most aggregation functions are designed to work with numeric fields and unexpected results can occur if you try to aggregate strings.

String Functions

String functions operate on single text strings and are used to obtain and manipulate characters or character information, such as extracting a substring of one or more characters or trimming blank spaces from a field.

The functions available from this menu depend on the SQL variant and syntax used by the data source. Refer to the vendor's help or documentation for a full description of these functions.

Numeric Functions

Numeric functions operate on single numeric fields and are used to obtain simple mathematical information, such as the sign of a number, the next highest integer or its absolute value.

The functions available from this menu depend on the SQL variant and syntax used by the data source. Refer to the vendor's help or documentation for a full description of these functions.

Date Part Functions

Date Part functions operate on date fields and are used to extract and convert a portion of a date into a character string such as day of week, day of year, quarter, hour and minute.

The functions available from this menu depend on the SQL variant and syntax used by the data source. Those shown may only be the most commonly used of those available. Refer to the vendor's help or documentation that was provided for a full description of these functions.

Date Name Functions

Date Name functions operate on date fields and are used to extract and convert a portion of a date into a named date element such as month name or weekday name.

The functions available from this menu depend on the SQL variant and syntax used by the data source. Refer to the vendor's help or documentation for a full description of these functions.

Conversion to Character Functions

Conversion to Character functions convert numeric fields to text fields, then apply the selected formatting to enable different formats for currency and dates and times.

The functions available from this menu depend on the SQL variant and syntax used by the data source. Refer to the vendor's help or documentation for a full description of these functions.

Conversion to Number Functions

Conversion to Number functions convert numbers held as text strings to their numeric values. Strings containing non-numeric values will be converted to 0.

The functions available from this menu depend on the SQL variant and syntax used by the data source. Refer to the vendor's help or documentation for a full description of these functions.

Conversion to Date Functions

Conversion to Date functions convert numeric fields to their date equivalent in the selected format.

The functions available from this menu depend on the SQL variant and syntax used by the data source. Refer to the vendor's help or documentation for a full description of these functions.

NULL Case

This button inserts code that will deal with a field containing a NULL value. Different SQL data sources will handle this differently, but should allow you to substitute some other value into the field if a NULL value is encountered. For example, in Access it might be:

IIf(IsNull(Fld), vNullCaseValue, Fld)

Whereas in Oracle it might be:

NVL(Fld, vNullCaseValue)

In both cases you replace vNullCaseValue with the value to be displayed (or further processing to be performed) if NULL is returned.

Simple Case

This button inserts code that deals with a simple either/or condition. Different SQL data sources handle this differently. For example, in Access it might be:

IIf(Fld = when_expression_1, result_expression_1, else_expression)

Whereas in Oracle it might be:

DECODE(Fld, when_expression_1, result_expression_1, else_expression)

Working in the Relations Pane

Once you have connected to a data source, your connection is displayed in the upper-right pane. You can set up and save as many data source connections as you need. Integration Querybuilder stores all connections you create unless you explicitly delete them.

Creating Queries on the Relations Pane

To create new queries on the Relations pane:

  1. Double-click the connection you want to use. The DB Browser in the lower-right pane displays the database.
  2. Click the database icon to display the icons for tables, views and procedures for this database.
  3. Select New Query from the Query menu. A new query icon opens beneath the connection icon in the upper-right pane.
  4. Drag the tables and views you want to use into the Relations (upper left) pane. SELECT… FROM statements are created in the SQL pane. If tables are already linked in the database, these links are displayed, although these can be changed or removed for the purpose of this particular query.

    If you are using a table more than once, the second and further copies are renamed. For example, if you already have a Customer table in the Relations pane and you drag across another copy, it is automatically renamed Customer1.

    NOTE: Although views can be used for creating relationships if supported by the database you are using, for ease of reading, this section only refer to tables.

Creating a Join in the Relations Pane

When two tables each contain a column that shares a common set of values, they can be linked on this column by a join. For example, a table of orders and a table of customers could be linked if both contain a Customer ID column.

To create a join between two tables in the Relations pane:

  1. Click and drag the common column to the common column in the other table. A join line now connects the two columns and a JOIN statement inserted into the FROM clause in the SQL pane.

    If the fields you have connected were set as primary and foreign keys in the original database, "key" and "infinity" icons appear at either end of the join. If these icons do not appear it may be that the two columns do not contain common values.

    The default join between two tables is called an Inner Join. This means that the query only returns those records where the fields on both sides of the join match. Unmatched records are discarded.
  2. To change the join type, right-click the diamond icon in the middle of the join.

You can change the join to any of the following:

Available Joins and Their Descriptions

Left Outer Join
All records in the table on the left side of the join are returned, even if there is no matching record on the right side. To create, right-click the diamond icon and click the first Select All Rows From option.
Right Outer Join
All records in the table on the right side of the join are returned, even if there is no matching record on the left side. To create, right-click the diamond icon and click the second Select All Rows From… option.
Full Outer Join
If full joins are permitted by the database connection, then all records from both tables are returned, regardless of matching records in the other table. To select this, open the Join Line Properties dialog box by right-clicking the diamond icon and selecting Properties. Check both Include Rows boxes.

You can also create joins that are based on other logical conditions than the two selected columns being equal. Do this by right-clicking the diamond and selecting Properties. This is not recommended unless there is no other way to create a query and you know exactly what you are doing, as it can produce an extremely large number of records.

Selecting a Field in the Columns Pane

The Columns pane allows you to select the fields you want to use in your query. If field names are more than one word long they are displayed in the column cells surrounded by square brackets.

City
[Customer ID]

In the column list, fields are listed by the table they appear in and names are not bracketed.

Customers.City
Orders.Customer ID

An asterisk (*) is used to indicate all fields, so Employees.* means all fields in the Employees table.

To select a field in the Columns pane:

  1. Click in the first empty cell beneath Column. Click the arrow to display the list of fields.
  2. Scroll to the field you want to use and click it. The field name appears in the cell and in the SELECT statement in the SQL pane.
  3. If you want to use a different name for this field when the query is generated, type the new name in the Alias cell.
  4. Select or clear the Output check box.
  5. If you want to sort the records by this field, click in the Sort Type cell and select either Ascending or Descending.
  6. If you are sorting on more than one field, enter the sort order ranking for this field in the Sort Order cell. For example, if you were sorting by five fields, you would select "1" if this were the first field to sort on or "5" if it were the last.
  7. Select the Group By check box to group identical occurrences of this field.
  8. Specify the criterion, if any, to be applied to the Criteria cell. For example, if you had a Total Cost field and only wanted to return items where the total cost was more than $30.00 you would enter ">30.00" here. The code generated by your criteria will be displayed in the SQL pane as a WHERE clause.

The value you use in a Criteria cell can be preceded by one of the following operators: "=","<", ">", "<="," >=", "!<", "!>", "!=", "<>", "IS", "IS NOT", "NOT", "IN", "LIKE", "BETWEEN". If no operator is entered, the default is "=".

Values following the LIKE operator will be put into quotes unless already so. The LIKE operator enables you to use regular expressions.

The wildcards and syntax available for use in regular expressions will depend upon your source database, so you should refer to the documentation supplied for further information. They should not be confused with those used in RIFL regular expressions.

Each time you define a criterion, another criterion cell opens, enabling you to set up a string of OR conditions for this column. For example, to select records where the Employee ID is less than 10000, between 10500 and 10750 or greater than 20000:

Which will result in the following SQL clause:

WHERE
([Employee ID] < 10000 OR
[Employee ID] BETWEEN 10500 AND 10750 OR
[Employee ID] > 20000)

OR criteria are created horizontally, whereas AND criteria vertically. For example, to select records where the Employee ID is greater than 10000 and the Last Name is not Smith:

Which will result in the following SQL clause:

WHERE
([Employee ID] > 10000 AND
[Last Name] <> "Smith")

To set up an OR criteria that tests two separate fields, do the following:

Which will result in the following SQL clause:

WHERE
([Balance] > 400 OR
[Last Payment] > 100)

To combine OR and AND criteria, remember that criteria are grouped with the criteria in the same column. As in the following example:

WHERE
(Employee ID] > 10000 AND
[Last Name] = "Smith" OR
[Last Name] ="Jones")

[Employee ID] > 10000 is only used with [Last Name] = "Smith". Records will be returned when the [Last Name] ="Jones" regardless of employee ID.

To return all records where the customer ID is greater than 20000 and the company name is Smith, Jones or Featherstone you will need to set up the following criteria:

WHERE
([Employee ID] > 20000 AND
[Last Name] = "Smith" OR
[Employee ID] > 20000 AND
[Last Name] = "Jones" OR
[Employee ID] > 20000 AND
[Last Name] = "Featherstone")


Working in the SQL Pane

Integration Querybuilder's SQL pane displays the SQL statement as you create it using the Relations and Columns panes. However, it can also be used by more experienced users as a SQL editor to create queries that cannot be put together simply using the Relations and Columns panes.

Caution: If you are not familiar with writing SQL by hand, do not edit the code Querybuilder generates, as changes made in the SQL pane override the settings you created in the Relations and Columns panes, and you might have to begin the query again.

Incorporating SQL Functions

To use SQL functions:

  1. In the SQL Pane, click the cursor at the appropriate point in the SQL statement where you want the new function to be inserted. You can create new lined, press the TAB key and insert blank spaces as with any text editor to improve the readability of the code.
  2. In the Column pane, select the required field or fields. Hold down the CTRL key and click each field if you are using a function that concatenates multiple fields.
  3. Select the required function from the SQL functions menu.
    4. Caution: If you have not selected a field or fields in the Column pane, the field or fields highlighted in the Relations pane are used even if you have not selected this field or fields. If you select any code in the SQL Pane prior to selecting a function, this code is overwritten.
  4. If you have put a new statement after an existing statement in the SELECT section, insert a comma after the existing code. Because of the way Querybuilder validates code, you have to add the comma after you have created the function.
    5. The selected function, with the field name(s) inserted, is created at the cursor point.
  5. Test the validity of your SQL query by clicking the Check SQL Syntax icon.
    If the syntax is valid, a new line containing this function is created in the Columns pane. Enter a short, meaningful name for it in the Alias cell. If the syntax is invalid an error message displays. The Relations and Columns pane is grayed-out until the SQL code is corrected and validated again.

Defining SQL Manually

To manually define SQL:

If you are an experienced SQL user, type query statements directly into Integration Querybuilder using this pane as an editor and the Check SQL Syntax button to validate the code as you build it. You can also paste text into the SQL pane from external programs, online help and e-mails.

If you copy existing material into the SQL pane, you must remove any comments, as these will cause an error when the query is exported to Map Designer.

If you set up a join conditions in the SQL pane rather than in the Relations pane and the join condition has an expression instead of a field on one side, the join line (in the Relations pane) will is drawn from a field of one object to the title bar of the other. The diamond icon will display "f*". If both sides of the join condition are expressions rather than fields the join line is drawn between the title bars of both objects.

Querybuilder does not validate or run any query containing DJX. Test and validate your statement using dummy information and substitute the DJX code in Map Designer's Query Statement box afterwards.

To run the query:

  1. Click the Execute Query button.
    The Result tab displays showing the result of this query. If the query takes a noticeable time to execute, the Stop button becomes active so that the query can be canceled if required.
  2. Toggle between the Query and Result tabs of Integration Querybuilder if you need to fine-tune the query and check the result after each adjustment.
  3. Once you are satisfied with the query you have created, click OK (located at the bottom of the window). Save the query with a meaningful name.
  4. Integration Querybuilder closes and the generated SQL code is copied into the SQL Statement box. To see the code formatted, click the ellipsis to display it in a separate window.
‹ Saving the Join Designup