Return
-
Title
Why Toad Data Point 6.0.0 was removed from the portal? -
Description
In Toad Data Point 6.0.0, manipulating Join conditions within the Visual Query Builder Diagram can result in incorrect SQL.
-
Cause
In Toad Data Point Version 6.0.0 introduced a bug that can cause a Left Outer Join to incorrectly be written as a Right Outer Join.
Meaning, when a user selects on the Diagram tab of the Visual Query Builder to select all of the rows from a particular table (which changes the nature of the ‘Join’ between the two tables from an ‘Inner Join’ to an ‘Outer Join’) the bug was causing the wrong type of Outer Join to be written in the resulting SQL. This will cause an incorrect result set.
We have included some simple examples of how Queries with Joins work below and how they are written correctly.Simple Join Examples with Data
In this example, we show 3 tables – Table_A, Table_B and Table_C
They have the following records:
Inner Join
If we create a query with all “Inner Joins” like below we will only return records that match between all 3 tables – specifically rows with ID 1,2,3.
The SQL for this query is:
SELECT Table_A.ID,
Table_A.A_Name1,
Table_B.ID,
Table_B.B_Name1,
Table_C.ID,
Table_C.C_Name1
FROM (quest_dev.dbo.Table_A Table_A
INNER JOIN quest_dev.dbo.Table_B Table_B ON (Table_A.ID = Table_B.ID))
INNER JOIN quest_dev.dbo.Table_C Table_C ON (Table_B.ID = Table_C.ID)
Outer Joins
Example 1 – Pulling all rows from Table_B
If we create a query where we want all the records from Table_B and only records from Table_A and Table_C , we will return all 6 rows from Table_B and the matching info from Table_A and Table_C.
The SQL for this query is
SELECT Table_A.ID,
Table_A.A_Name1,
Table_B.ID,
Table_B.B_Name1,
Table_C.ID,
Table_C.C_Name1
FROM (
quest_dev.dbo.Table_B Table_B
LEFT OUTER JOIN quest_dev.dbo.Table_C Table_C
ON (Table_B.ID = Table_C.ID))
LEFT OUTER JOIN quest_dev.dbo.Table_A Table_A
ON (Table_A.ID = Table_B.ID)
Example 2 – Pulling all rows from Table_C
And finally, if we rewrite the query to pull all rows from Table_C, we will return 9 rows of data.
The SQL for this query is
SELECT Table_A.ID,
Table_A.A_Name1,
Table_B.ID,
Table_B.B_Name1,
Table_C.ID,
Table_C.C_Name1
FROM (
quest_dev.dbo.Table_A Table_A
RIGHT OUTER JOIN quest_dev.dbo.Table_C Table_C
ON (Table_A.ID = Table_C.ID))
LEFT OUTER JOIN quest_dev.dbo.Table_B Table_B
ON (Table_B.ID = Table_C.ID)
-
Resolution
Step 1) Download Toad Data Point V6.0.5 to correct this issue.
ttps://support.quest.com/toad-data-point/6.0.5/download-new-releases
Step 2) If you installed Toad Data Point v6.0.0 and created or edited Visual Query Builders that use NON-INNER Joins, please revisit these Queries and confirm that they are correct.
-
Defect ID
QAT-17571