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.