Query Builder Pane

Query Builder Pane

Query Builder Pane.

Identify the tables required for the query in the Data Source Tree by searching and selecting as described above.

Then drag the table from the Data Source Tree and drop it into the Query Builder Pane.

In the example Tables starting F42 have been filtered and Table F4211 – Sales_Order_Detail_File has been selected and dragged into the Query Build Pane.

Further tables can then be selected and dragged and dropped into the Query Build pane.  There is no limit to the number of tables that can be specified other then the ability to manage those selected.

As each table is dropped into the Query Build Pane, details are displayed in SQL format in the SQL Source Pane.

Having selected the table/s, check the columns in each table that you want to appear in the Results Pane.

The Column details are displayed in SQL format in both the Sort and Criteria Pane and the SQL Source Pane.

The columns appear in the two panes in the sequence in which they were selected.  However, they can be de-selected and reselected to appear as the last column or they can be selected by left clicking in the extreme left column of the Sort and Criteria Pane row and dragging the field to any required position.

If all the columns in a table are required to be selected, then check the first field in the table shown *.  All fields will then be selected.  It is not possible to select all fields then deselect those not required; it is all or nothing.

All Columns selected are displayed in the Sort and Criteria Pane with a in the output column.

If a Column is not required for output in the Results Pane but is required for Sorting and Selecting, then the Output column can be unchecked (as shown above) and the Sort type, Sort Order and selection Columns selected with details.  The options available are described in full below under Sort and Criteria Pane.

An alternative method of selecting a field for display is to highlight the field in the table in the Query Builder Pane and click and drag it into the Sort and Criteria Pane.  With this method of selection the Select Field Check Button will be automatically checked.

Table Joins

If more than one table is dragged into the Query Builder Pane then they must be joined.  Tables must be joined by dragging columns from one table to another to define the joins.  If more than one table is selected then there must be at least one join.

Creating a table join indicates an association between a field in one table with a field of the same data type within the second table.

When a link is created between two tables a black line will appear between the two fields.

A table join may be removed by highlighting the link and pressing Delete. An alternate method to remove a link is to Right Click on the join and a pop up box appears with the option to Remove Join; Click on Remove join and the link is deleted.

There are four basic types of join:

       INNER JOIN – the default

       LEFT OUTER JOIN

       RIGHT OUTER JOIN

       FULL OUTER JOIN

The type of join is selected (or changed) by Right Clicking on the join to display the pop up box (shown above) with the Join Properties option. Select Join Properties and the Object Properties Join box appears.

Object Properties Join Options

       Join Operator (For Selected Join)

               Displays Table details - F4211_Sales_Order_Detail and F4201_sales_Order_Header

               Displays Column details - SDDOCO_Order_Number and SHDOCO_Order_Number

               Display default = operator (may be changed to one of <>, <, <=, >, >=)

       Include Rows

All from F4211_Sales_Order_Detail_File

All from F4201_Sales_Order_Header_File

       Both check boxes are blank on entry.  This is the default join (An INNER JOIN) and only rows that have a matching record in both files will be selected.

       An Inner join is the standard type of join. The result set from an Inner join includes all the records in which the linked field value in both tables is an exact match.  For instance, you can use an Inner join to view all customers and the orders they have placed. You will not get a match for any customer who has not placed orders.

Check option from F4211 for a LEFT OUTER join.

The result set from a Left Outer join includes all the records in which the linked field value in both tables is an exact match.  It also includes a row for every record in the primary (left) table for which the linked field value has no match in the lookup table.  For instance, you can use a Left Outer join to view all customers and the orders they have placed, but you also get a row for every customer who has not placed any orders. These customers appear at the end of the list with blanks in the fields that would otherwise hold order information:

Check option from F4201 for a RIGHT OUTER join.

The result set from a Right Outer join includes all the records in which the linked field value in both tables is an exact match.  It also includes a row for every record in the lookup (right) table for which the linked field value has no match in the primary table.  If you link the Customer table to the Orders table, you get one row in the table for each order a customer has placed.  You also get a row for every order found that cannot be linked to a customer.  Theoretically, this should not happen, but if an inexperienced sales person forgot to assign a customer ID to an order, you can quickly locate that order with a Right Outer join.  The resulting table leaves a blank in any of the Customer fields for the order without a customer.

Check both options for a FULL OUTER join and all records in both files will be selected in the results.

A Full Outer join is a bidirectional outer join where you can see all records in your linked tables.  The result set from a Full Outer join includes all the records in which the linked field value in both tables is an exact match.  It also includes a row for every record in the primary (left) table for which the linked field value has no match in the lookup table, and a row for every record in the lookup (right) table for which the linked field value has no match in the primary table.  If you link the Customer table to the Orders table, you get one row in the table for each order a customer has placed. You also get a row for every order found that cannot be linked to a customer, and a row for every customer for whom an order cannot be found.

The table below shows in simplified terms exactly what this means.  All joins are with the = operator.

Assume there are 10 rows in Table F4211 and 6 rows in Table F4201 as shown

NOTE: Currently utbDirect does NOT support ‘Full Outer Joins’ when used with the jdeDirect ODBC driver.