Table of Contents

Define a Join Manually

If you have more than one table, you must define at least one join condition for each table.

If there are no Suggested Joins or you need to edit your join:

  1. Click the Actual Joins tab:
  2. Select the type of join under the Join Type column
  3. Enter the first table in Table 1
  4. Enter the first field in Field 1
  5. Enter the Relation (this is normally =)
  6. Enter the second table in Table 2
  7. Enter the second field in Field 2

Join concepts

There are four join types available: SIMPLE, LEFT OUTER, RIGHT OUTER, and SQL 92 LEFT OUTER.

SIMPLE JOIN

A SIMPLE join is a one-to-one relation: the value for Table 1 and Field 1 MUST match the value of Table 2 and Field 2 (these examples presume the Relation field is "="). When this match occurs the result is one row for that match. If the target table does not have a match for the source table row, then no row is returned to the grid.

LEFT OUTER JOIN

A LEFT OUTER join is a one-to-many relation: the value for Table 1 and Field 1 matches the value of Table 2 and Field 2. Unlike the SIMPLE join, if the Table 2 and Field 2 value does not match, then the query still returns a row for Table 1 and Field 1. The Table 2 fields in this instance will all be null (you can search for null fields using the <BLANK> filter). Use a LEFT OUTER join for:

  1. Drill down reports - If you do a LEFT OUTER join between a header table and a detail table, then your report will be able to drill down between the header (summary) records and the detail records.
  2. Integrity reports - If you have a header table and want to know which headers have no child records, then use a LEFT OUTER join.
Important

Any filter on the secondary table (right table) will have 'OR NULL' added to it. This is to ensure that unmatched primary (left table) records are also returned.
This also ensures that row or masking security filters do not unexpectedly cause unmatched primary table rows to be omitted.

RIGHT OUTER JOIN

A RIGHT OUTER join is the same thing as a LEFT OUTER join except Table 1 and Table 2 are swapped. Use a RIGHT OUTER join, for instance, to identify detail rows that do not have a header row.

Important

Any filter on the secondary (left table) table will have 'OR NULL' added to it. This is to ensure that unmatched primary (right table) records are also returned.
This also ensures that row or masking security filters do not unexpectedly cause unmatched primary table rows to be omitted.

SQL 92 LEFT OUTER JOIN

The above outer joins are not SQL 92 compliant. Those joins will add an 'OR NULL' clause to all filtering on the secondary table. This is to ensure that unmatched primary records are also returned. However, in some cases, a SQL 92 compliant outer join is required. In such cases, use this join type but be aware that row or masking security filters will cause unmatched primary table rows to not appear in the results.

Note

This join type is only available for JDE E1 8.11 and later.

Special Considerations when Converting from a Native E1 Business View

Unmatched rows in non-string columns (such as numeric or date) will show blank instead of a default value (like zero) when converting a native E1 view to a DASVIEW. A SQL 92 LEFT OUTER JOIN will behave like native JDE views and continue to show default values for unmatched rows.