Toad World® Forums

SQL Builder Suggestions


#1

A couple of items on the SQL Builder.

First, it would be lovely if pressing the tab key moved me one column to the right, staying within the same row. It’s a nuisance to have to take my hands off the keyboard, grab my mouse, point to the next cell, click the mouse button, and then start typing again. Major slow-down for me, and it has been the cause of some jeering at TOAD.

Second, I just created a view. The view combines a master table with several “satellite” one-to-many tables. These tables each look at (different) values in another table. Like so:

Table1 - Table2 - Table3a
- Table4 - Table3b

I added Table3 in twice, and aliased the table appropriately. Then I added in the columns from that table. Then I had to go and alias every single one of those columns, with a common prefix, to be able to ID which value went with which ancestor table. Major pain! It would be nice to be able to tell TOAD to add a common prefix to each column name in a given table in the SQL Builder.

Does this make sense?


#2

I am sorry but I don’t understand the first request. Where did you type? Could you please provide more detailed description?

Regarding the second proposal –it’s already here. Please open Tools | Options | Database | Query Builder and check “Use fully qualified column names” check box. It should solve the issue.

Regards

Aleksey


#3

Aleksey -
I will address the second item first: This is not what I am referring to. I already have that checked.

Sample psuedo-query (it won’t run, but it should give you the gist of what I am trying to do):
SELECT ItemName, ItemStatus.ItemStatus, ColorStatus.ItemColorStatus, TypeStatus.ItemTypeStatus
FROM Items
INNER JOIN ItemTypes ON ItemID = TypeID
INNER JOIN ItemColors ON ItemID = ColorID
INNER JOIN Status AS ItemStatus ON ItemID = StatusID
INNER JOIN Status AS ColorStatus ON ColorID = StatusID
INNER JOIN Status AS TypeStatus ON TypeID = StatusID

So, table Status is in there three times. I am selecting the same column(s) from Status each time. I do not want to end up with Status_1, Statuts_2, Status_3, etc. When I alias the table, it would be nice to have the OPTION to cause all columns in that table to have a common prefix. So, when I alias Status to be ItemStatus, I would optionally request that all columns that I add from that table be prefixed with ItemStatus. When I alias Status to be ColorStatus, I would optionally request that all columns I add from that table be prefixed with ColorStatus. And so on and so forth.

For the first, I am in the column grid, on the designer. I had to rename all the columns in two or three tables, so that I did not end up with Col1_1, Col2_1, etc. It was a pain to have to type in the new aliased column name, and then click in the next cell. I just wanted to be able to Tab to the next cell to the right. That would have made it quicker and easier to alias all those column names. Quite frankly, being able to tab through the cells in the column grid on the designer is, to me, more important than pre-pending a common name.

Does this clear things up?


#4

I get you point about typing alias for the columns. I’ll create a CR to track the request.

Regarding the table alias - the aliases can be renamed. So in your case you can name alias for the first Status table as ItemStatus, the second one as ColorStatus and so on.
For example I dropped a table [address] on Query Builder twice and join them, renamed the aliases to AddressOne and AddressTwo and get the following SQL

SELECT AddressOne.ADDRESS,
AddressTwo.CITY,
AddressTwo.ADDRESS,
AddressTwo.ADDRESS_LINE_2,
AddressOne.ADDRESS_LINE_2,
AddressOne.CITY
FROM QUEST_STAGE.dbo.address AddressOne INNER JOIN QUEST_STAGE.dbo.address AddressTwo
ON (AddressOne.ADDRESS_ID = ddressTwo.ADDRESS_ID)

As you can see the AddressOne prefixes the columns from this table.

Does it help?

Regards
Aleksey


#5

Aleksey,
I look forward to tabbing coming soon, then…

As for the second part of your reply:
I am not referring to table aliasing. I am referring to column aliasing. I think table aliasing is handled very nicely right now. I type in the table alias, and BAM - all the column calls are properly aliased! That’s great!

The problem with your query is two-fold:

  1. You cannot convert it into a view, as all column names must be unique. ADDRES_LINE_2 and CITY are not unique. Those columns MUST be aliased, if the query is to be used in a view.
  2. In the data grid, you will find that there is no table alias shown, and the actual output is:
    CITY
    ADDRESS
    ADDRESS_LINE_2
    ADDRESS_LINE_2_1
    CITY_1

While having an “_1” appended to the name is OK for tests while doing development work, and for finding accidental duplicate column names (it’s really quite useful for this, actually), it is not OK for production work. The columns need to be properly aliased for production work. The way I handle that is to prepend something common (and that, hopefully, makes sense) to the front of the column name. So, for instance, I might rewrite your query as follows:
SELECT AddressOne.A1ADDRESS,
AddressTwo.A2CITY,
AddressTwo.A2ADDRESS,
AddressTwo.A2ADDRESS_LINE_2,
AddressOne.A1ADDRESS_LINE_2,
AddressOne.A1CITY
FROM QUEST_STAGE.dbo.address AddressOne INNER JOIN QUEST_STAGE.dbo.address AddressTwo
ON (AddressOne.ADDRESS_ID = AddressTwo.ADDRESS_ID)

I have prepended “A1” or “A2” to each of the columns, based on which table they are coming from. This allows me to create a view from the query, and still retain knowledge of which column came from where. It would be nice to be able to do that common column alias prepend automatically, rather than having to type in the column alias for every single column. Again, this should be optional, not mandatory.

Does this help to clarify what I am trying to do?


#6

It looks like finally I get it.

You would like to get column aliases to be OPTIONALY auto generated bases on the following template customer prefix (can be the table alias) + column name.

So the my query becomes

SELECT
AddressOne.ADDRESS as AddressOneADDRESS ,
AddressTwo.CITY as AddressTwoCITY,
AddressTwo.ADDRESS as AddressTwoADDRESS,
AddressTwo.ADDRESS_LINE_2 as AddressTwoADDRESS_LINE_2,
** AddressOne**.ADDRESS_LINE_2 as AddressOneADDRESS_LINE_2,
AddressOne.CITY as AddressOneCITY
FROM QUEST_STAGE.dbo.address AddressOne
INNER JOIN QUEST_STAGE.dbo.address AddressTwo
ON (AddressOne.ADDRESS_ID = AddressTwo.ADDRESS_ID)

Now you have not to type the column aliases for every column.
Am I right?
Please let me know that I got everything correctly and I’ll create a CR.

FYI – The CR # 107096 has been created to track the first enhancement.

Regards

Aleksey


#7

Exactly! Thanks Aleksey!


#8

Perfect!

I created the CR 107119.

Thanks
Aleksey