TDP 5.0 Workbook Outer Join

Toad Data Point & Toad Intelligence Central
1

Using TDP Workbook 5.0.4.45, trying to create an outer join as shown in the screen shot below. Couldn't figure out how to create this join in Diagram tab but was able to enter the SQL manually in Query tab. When I execute the query it runs correctly but I'm getting an error when trying to synchronize query and diagram. Also I cannot save the workbook since the query and diagram are not synced.

outer%20join
outer join.jpg836×206 31.3 KB

2

Hi Steve,
In Query Builder you connect columns with an arrow.

image
image.png719×358 10.6 KB

In the middle of the arrow you can see an inner join icon - if you right-click you can select right or left outer join by selecting all columns from either of the tables.
image
The left side is where the arrow is starting and the right side is the end of the arrow.
Currently, the order of the tables in the Venn Diagram window does not respect the order in this relation.

More details are in the Help topic - Join Columns and Analyze Joins
Does it answer your question?

3

My question is about a complex join where there is an additional limit besides the outer join between the 2 tables. In the screen shot I sent in the initial post, there is an outer join between VEND.VEND_ID and VEND_ADDR.VEND_ID but also an additional limit that needs to be part of the outer join: VEND_ADDR.S_PMT_ADDR_CD='Y'. In reading some other posts on this subject, I tried creating a calculated field VEND.S_PMT_ADDR_CD and setting it equal to 'Y' and then joining it to VEND_ADDR.S_PMT_ADDR_CD and that seems to work. Is that the recommended way to implement the complex outer join?

4

Hi Steve,
yes, since that is really unsupported in Query Builder, using calculated fields is the recommended way.
Could you add your suggestion to idea pond where the product management might decide about possible improvements?

5

Steve,

Is the goal to first return every vendor_id and name, and second to return the address only if the address is flagged Y?

If this is case I would try starting with a new query builder and:

  1. Add VEND as the only table in the query.

  2. Select VEND_ID and VEND_NAME.

  3. Click Add a subquery to the FROM clause on the toolbar (button to the left of the Edit Calculated Field button). This will cause a new query builder window to popup.

  4. Add the VEND_ADDR table to the subquery.

  5. Select VEND_ID, CITY_NAME, and S_PMT_ADDR_CD; set S_PMT_ADDR_CD to be = 'Y'. This query can be tested/run by itself, if desired.

  6. In the new Queries window that was created ( to the left ) when the subquery was added, click to return to the main "Query".

  7. Now you will be back in the first query and you will see an object that looks like a table object that represents the Subquery. Join these tables together on VEND_ID and right-click the join so that all rows from VEND return.

  8. Click in the Subquery table to select the CITY_NAME to be in the results. The query can now be run. Optional: you can edit the Table Alias of CITY_NAME and this will rename it from Subquery to something more meaningful, e.g., PMT_ADDRS (handy in more complex scenarios).

6

I tried your suggestion and it seems to work correctly. Thanks for your assistance.