Alchemex Graphical Joins (Joining Tables)

From Sage Intelligence Knowledgebase
Jump to: navigation, search

Using the Alchemex Administrator tool, Data is made available through the addition of Data Containers and relevant Expressions.

The source of the underlying data can be a Table, a View, a Stored Procedure or a user defined SQL Join or a Graphical Join.

To create a Container based on a SQL Join, the Administrator should add a data container, selecting the option “SQL Join” from the Select Container Type window and providing a name for the container.

Then, before adding Expressions, you would type in the join syntax in the Source Container (Join) field. The Source should thus be the FROM clause (excluding the FROM keyword) of a SQL query that would define the join. The syntax of the Join can be verified by using the Check/Test facility for the Container.

When the SQL join is verified to be correct, the Administrator can choose to Add Expressions.

The expression list will then include all Fields from all the Tables in the Join.

There are two main SQL Join styles that are used by Database Systems.

For some systems either will work while for others only one of the methods might work.

It is recommended where possible to use Syntax 2 (the newer ANSI style Join).

Join Syntax 1

TABLE1, TABLE2, TABLE3

WHERE TABLE1.KEYFIELD = TABLE2.KEYFIELD

AND TABLE2.KEYFIELD_2 = TABLE3.KEYFIELD

e.g.

timesheet, employee, department

WHERE timesheet.employee_id = employee.id

AND employee.department_id = department.id

Join Syntax 2

TABLE1

[INNER | LEFT | RIGHT] JOIN TABLE2

ON TABLE1.KEYFIELD = TABLE2.KEYFIELD

[INNER | LEFT | RIGHT] JOIN TABLE3

AND TABLE2.KEYFIELD_2 = TABLE3.KEYFIELD

e.g.

timesheet

INNER JOIN employee ON timesheet.employee_id = employee.id

INNER JOIN department

ON employee.department_id = department.id

INNER JOIN ON

NOTE: Some systems allow tables to have names, which include space characters. Where this is the case then it is necessary to surround table names and field names with square brackets. The matrix below shows which styles of joins are supported by the most common Database Systems

Alchemex Graphical Joins

Creating a Graphical Join

To create a Container based on a Graphical Join, the Administrator should add a data container, selecting the option “Graphical Join” from the Select Container Type window and providing a name for the container.

Then, before adding Expressions, the Administrator would create the joins between the tables by adding tables within the Graphical Join tool, then dragging the relevant field from one table and dropping it onto the appropriate field in the joining table.

The illustration below displays two joins between 3 tables.

Alchemex Graphical Joins

An Inner Join is created by default, which can be changed by right clicking on the join box on either side of the join and selecting Outer join.

The reverse polarity option on the shortcut menu switches the join between a Left or Right Join.

The SQL syntax for the join can be viewed by selecting the check box “Show SQL”

Once the joins have been created they should be verified by using the Check/Test facility for the Container.

On closing of the Graphical Join tool the join will be displayed as a SQL syntax.

When the SQL join is verified to be correct, the Administrator can choose to Add Expressions.

Changing from a Join to a Graphical Join

You may change existing joins to a Graphical Joins but on doing so you will have to recreate the join within the Graphical Join tool.

To change the source container type from a Join to a Graphical Join, select the arrow in the Source container type field and select Graphical Join.

Then click the apply button.

The container properties window will now display a Graphical Join Tool button.

Alchemex Graphical Joins

Select the Graphical Join tool button, add the required tables, create the joins then click the apply button to accept your changes