Toad World® Forums

TDA Query Builder doesn't quote table in FROM clause


#1

I wanted to know if the following was a bug or known/as-designed behavior:
TDA doesn’t automatically add quotes to the table name in the FROM clause if “Use Fully Qualified Object Names” is disabled. I’m using TDA v2.5.1 and seems to occur regardless of source data type (i’ve tried Oracle, SQL, odbc).

When I create a simple query with the builder and the FQ Object Names setting disabled, TDA doesn’t quote the first instance of the table name. This will result in query failing. Ex:

SELECT “ID”
FROM TABLE A “TABLE A”
WHERE (“ID” = ‘1’)
If the FQ Object Names query builder option is enabled, then TDA will quote both table names. However, this isn’t a workable solution for all data sources: ODBC in particular (at least the couple drivers I use) will fail due to the extra data.
SELECT “ID”
FROM “DMS01”.“TABLE A” “TABLE A”
WHERE (“ID” = ‘1’)

A secondary question: what is the purpose of having the table name repeated? I am not performing a join. When i manually write basic queries I’ve never listed tables twice (and i tweak the query string in TDA to remove the double-table). I have seen other builder tools do the same table name duplication in the FROM clause, so there has to be a reason…right? :slight_smile:


#2

That is an oversight on our part. I have created the following CR for this issue.

CR71449 - Query Builder: Table name is not quoted unless fully qualified option is enabled.

For your second question, the duplicate table name is the table’s alias. Most query builders seem to add it. It looks redundant because the alias is set to the original table’s name. It can be changed by right-clicking on the title bar of the table in the diagram and selecting the Properties menu item. This brings up a dialog where you can enter a different alias. We don’t have an option to turn off automatic table alias generation.

Shawn…


#3

Thanks for opening a ticket Shawn! That answers the first part then; look forward for the fix. Having to constantly keep hand-adjusting the query string is a nuisance. :slight_smile:

Regarding the Alias portion of the answer: I guessed that it may have been aliasing; I’ve just always explicitly used the “AS” keyword.

e.g.
SELECT id, field1
FROM a_really_long_table_name as t1
WHERE t1.id = 5

Now I know exactly. Thanks! Marked item as answered!


#4

Any ETA on CR71449? I’ve updated to 2.6.2.580 and still not fixed. I know its a minor issue but it impactful because I have to hand-alter every query I perform.


#5

Looks like it still pending work. I pushed up the priority. :slight_smile:

Debbie