- Join Associations

Join Associations

[EE] One widely used feature of SQL lets you combine, or "join" data from two tables into a single

result table. If your data is on two separate systems however, you cannot use regular SQL to join it.

Using associated joins, WOW gives you the ability to join data from two separate systems.

As an example, say we have a table (CUSTOMER) on one system with columns ID, NAME, and BALANCE;

and another table (CUSTINFO) on a second system with columns ID and COLOR; and we want to join

the two table together on the ID column, letting the user view a customer’s name, ID, balance, and

favorite color all in a single table. (For our example, we will assume that field descriptors for both tables

have already been created, as described in the previous chapter, and that connections for both systems

have been created.) The first step is to create the "base" query. This is a normal SQL Operation, selecting

the rows of interest from a single table:

In our example, we are selecting all the rows, but you can use any type of WHERE clause you wanted

with this query. The next step is to create the "join" query – which should select all the rows from the

second table. Do not specify a WHERE clause in the join query. This operation’s type must Associated Join:

Note that you will want to specify a different connection alias for the join operation than you did for the

base operation since they are on two different systems.

Next, start the application and run the base operation (only data from one table should be retrieved):

Click the gear icon to edit the FD of the column you want to join the two tables on. This column must be

common to both tables. In our example, this is the ID column. In the Field Descriptor Manager window,

location the field descriptor’s association operation, and set it to

the Associated Join Operation we created earlier.

Next you must set your parent operation using a new PROPERTY JOIN to instruct WOW to navigate the association:

Add the following to your parent/main operation:

JOIN{checkAssocs:TRUE;}

For more details, see the JOIN property group.

Now when the base operation is run again, the results will a join between the two tables on different systems:

TAGS: Combining data

TODO: You can use global variables and include a WHERE clause in the JOIN ASSOCIATION

Example JOIN ASSOC:

select 'MS SQL' as DBSource, 'VENDOR' as Document2, v.vendorid, v.creditrating from dillsample.dbo.vendor v where vendorID = ??!VENDORID

The parent operation also includes field "VendorID" and declares it as a global variable.