Toad World® Forums

Cross-Connection Query Builder Visualize the SQL Statement

I have been having issues with the Cross-Connection Query Builder. I have several Oracle databases that I would like to connect and access with a single output and being new to the Diagram view, I have a tendancy to switch to the query tab and write what I want in SQL. The problem is that even though I am able to run these hand written SQL statements using the execute statement, I cannot save the Query builder unless I “Visualize” the statement. At times, Toad Datapoint (3.6) cannot viusualize it and rewrites half of the statement. The re-write is often nonsense and I loose my current working version. For example:

SELECT DISTINCT MAX(START_DATE) AS START_DATE

FROM DATABASE1.TABLE1 TABLE1

WHERE ((COL1 LIKE ‘%ARL%’ OR COL1 LIKE ‘%OKL%’) AND START_DATE > SYSDATE-5)

This simple query executes just fine, but when trying to save, TOAD sees a diagram versus SQL synch error and asks to visualise the statement. When it is “Visualized” and syntax error is thrown and this is the result.

– Correct the flagged problems and synchronize query and diagramm

– by pressing Visualize Query context menu item.

SELECT DISTINCT MAX(START_DATE) AS START_DATE

FROM DATABASE1.TABLE1 TABLE1

WHERE ((COL1 LIKE ‘%ARL%’ OR COL1 LIKE ‘%OKL%’) AND `DATAB)

Is there a way to rectify this?

Thank you.

I run into a similar issue constantly, which often prevents me from saving an accurate .tsm file. Is there any way to turn this off??

After failing to get any answers, I found a work around that may help. Instead of saving the .TSM file (requiring a visualization), cut and paste the user-editted sql into a new .SQL file. As long as the reference syntax is correct, Datapoint will run it as a Cross-Connection query. You will loose all the GUI benefits of the TSM, but at least you will be able to run and save the working script. (I get the correct syntax references by creating a dummy TSM report with something to the extent of a select * from every wanted table (Note: do not run this report)) Hope this helps. - Jon