Clicking Visualize creates duplicate columns in query

Toad for SQL Server
1

When I write a query with a sub-query and then click Visualize, some of the columns in the sub-query are duplicated. This causes an error when running the query. I am using v5.5.0.369 (64-bit).

Original SQL:
SELECT rm_TMRDAllProducts.[Request ID],
rm_TMRDAllProducts.[Request Stage],
ProductsComplete.[Last Date],
COUNT (rm_TMRDAllProducts.[Request ID]) AS [Num Products],
ProductsComplete.[Num Complete],
COUNT (rm_TMRDAllProducts.[Request ID])
- ProductsComplete.[Num Complete] AS Diff
FROM (SELECT rm_TMRDAllProducts.[Request ID],
MAX (rm_TMRDAllProducts.[Complete Date]) AS [Last Date],
COUNT (rm_TMRDAllProducts.[Request ID]) AS [Num Complete]
FROM OPTIMA3.hsi.rm_TMRDAllProducts rm_TMRDAllProducts
WHERE ( ( rm_TMRDAllProducts.Stage = 'Complete'
OR rm_TMRDAllProducts.Stage LIKE 'Cancel%')
AND ( rm_TMRDAllProducts.[Request Stage] <> 'Complete'
AND rm_TMRDAllProducts.[Request Stage] NOT LIKE 'Cancel%'))
GROUP BY rm_TMRDAllProducts.[Request ID]) ProductsComplete
LEFT OUTER JOIN OPTIMA3.hsi.rm_TMRDAllProducts rm_TMRDAllProducts
ON (ProductsComplete.[Request ID] = rm_TMRDAllProducts.[Request ID])
WHERE ( rm_TMRDAllProducts.[Request Stage] <> 'Complete'
AND rm_TMRDAllProducts.[Request Stage] NOT LIKE 'Cancel%')
GROUP BY rm_TMRDAllProducts.[Request ID],
rm_TMRDAllProducts.[Request Stage],
ProductsComplete.[Num Complete],
ProductsComplete.[Last Date]
HAVING ( COUNT (rm_TMRDAllProducts.[Request ID])
- ProductsComplete.[Num Complete] =
0)
ORDER BY ProductsComplete.[Last Date] DESC

After clicking Visualize:
SELECT rm_TMRDAllProducts.[Request ID],
rm_TMRDAllProducts.[Request Stage],
ProductsComplete.[Last Date],
COUNT (rm_TMRDAllProducts.[Request ID]) AS [Num Products],
ProductsComplete.[Num Complete],
COUNT (rm_TMRDAllProducts.[Request ID])
- ProductsComplete.[Num Complete] AS Diff
FROM (SELECT rm_TMRDAllProducts.[Request ID],
MAX (rm_TMRDAllProducts.[Complete Date]) AS [Last Date],
COUNT (rm_TMRDAllProducts.[Request ID]) AS [Num Complete],
rm_TMRDAllProducts.[Request ID] AS [Num Complete]
FROM OPTIMA3.hsi.rm_TMRDAllProducts rm_TMRDAllProducts
WHERE ( ( rm_TMRDAllProducts.Stage = 'Complete'
OR rm_TMRDAllProducts.Stage LIKE 'Cancel%')
AND ( rm_TMRDAllProducts.[Request Stage] <> 'Complete'
AND rm_TMRDAllProducts.[Request Stage] NOT LIKE 'Cancel%'))
GROUP BY rm_TMRDAllProducts.[Request ID]) ProductsComplete
LEFT OUTER JOIN OPTIMA3.hsi.rm_TMRDAllProducts rm_TMRDAllProducts
ON (ProductsComplete.[Request ID] = rm_TMRDAllProducts.[Request ID])
WHERE ( rm_TMRDAllProducts.[Request Stage] <> 'Complete'
AND rm_TMRDAllProducts.[Request Stage] NOT LIKE 'Cancel%')
GROUP BY rm_TMRDAllProducts.[Request ID],
rm_TMRDAllProducts.[Request Stage],
ProductsComplete.[Num Complete],
ProductsComplete.[Last Date]
HAVING ( COUNT (rm_TMRDAllProducts.[Request ID])
- ProductsComplete.[Num Complete] =
0)
ORDER BY ProductsComplete.[Last Date] DESC

2

Welcome to the Toad Data Point forum.

What are the steps used to produce this? Are you pasting your original query into the Query tab of the Visual Query Builder and pressing "Visualize" to get the graphical version of your query? If so, I can tell you that users will run into some issues.

In general, it's a MUCH harder problem to create a query diagram from SQL syntax vs. the other way around. In some cases, we simply can't generate a diagram equivalent to the SQL.

Please provide an outline of the steps you took to get from your query A to query B, and we'll investigate further. Might be that we'll need to take this one to Support.

3

I write the first query in one tab. I write the second query in another tab and test that both run successfully. I copy the second query into the first query and add the left outer join syntax. Then I click visualize. The query does visualize but clicking run produces the error "The column 'Num Complete' was specified multiple times. If I remove the duplicate column then the query runs but it can't be saved without being visualized. I end up visualizing and saving, then if I open the query later I need to remove the duplicate column(s) before running.