SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Accepting enhancement suggestions for new releases
Goto page Previous  1, 2, 3, ... 10, 11, 12  Next
 
This topic is locked: you cannot edit posts or make replies.    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
Accepting enhancement suggestions for new releases
Author Message
judahr



Joined: 09 Mar 2007
Posts: 319
Country: United States

Post Reply with quote
SysOp wrote:
I will be happy to provide an early private build to wet this new feature for usability and to receive your feedback as soon as this version becomes available.


Feel free to drop me an email when you have something to test, I'll try to take a look at it and provide feedback.
Thu Apr 03, 2008 1:58 pm View user's profile Send private message
hitesh



Joined: 15 Oct 2007
Posts: 104

Post SQL Server: INFORMATION_SCHEMA Reply with quote
HI,

I am using SQL Server 2005 Express Edition and SQL Assistance 3.1.22.

I have logged in as user 'sa'

What I have notice is that when I am typing a sql query (SELECT * FROM INFORMATION_SCHEMA.) on MASTER db, I get
intelisence popup list of SCHEMA objetcs like TABLES, COLUMNS etc (see SA1.jpg); but when i type the same SQL query on any other DB other than MASTER DB then it does not popups intelisence list on INFORMATION_SCHEMA. (see SA2.jpg)

Can you enable this in next release?

Thank You,

Hitesh




Fri Apr 04, 2008 8:18 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
This is not an SQL Assistant issue.

The database context query likely works in master database only and fails in all other databases. To verify that, paste the context query from SQL Assistant options to SQL Server Management Studio and execute it in different databases. I bet you will see some errors when you are not in master.
Fri Apr 04, 2008 10:40 am View user's profile Send private message
judahr



Joined: 09 Mar 2007
Posts: 319
Country: United States

Post Auto-Join Suggestion Reply with quote
Assuming:

Select *
From dbo.Table1 t
INNER JOIN dbo.

At this point, it would be great if you select a column instead of just a table it outputs the table name, alias, ON, the column names selected with the prefix, =, and if Table1 has a matching column name or a join, it outputs that as well. Examples in (computer) logic order:

It would append the following if Column1 was selected from Table2 and Table1 does not have column1 or a defined join:

Table2 t2 ON t.Column1 =

It would append the following if Column1 was selected from Table2 and if Table1 has both column1 and a defined join on the column, but different from column 1:

Table2 t2 ON t.Column1 = t.KeyRelationshipColumn

It would append the following if Column1 was selected from Table2 and Table1 does not have column1 but does have a defined join:

Table2 t2 ON t.Column1 = t.KeyRelationshipColumn

It would append the following if Column1 was selected from Table2 and Table1 also has column 1:

Table2 t2 ON t.Column1 = t2.Column1
Thu Apr 10, 2008 5:24 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Are you still running an old version?
Thu Apr 10, 2008 5:27 pm View user's profile Send private message
judahr



Joined: 09 Mar 2007
Posts: 319
Country: United States

Post Reply with quote
SysOp wrote:
Are you still running an old version?

3.1.24
Thu Apr 10, 2008 5:30 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Please disregard the previous post. From older posts it looks like you are using 3.1. Version 3.1 should be already doing all of that, even 3.0 could partially handle most of the described. When you select a "table" in the join, don't select a table on the second level, use the arrow right key to expand the level and then select the required join condition on the third level, the rest will be inserted automatically for you. It can handle both predefined joins (using foreign keys) and column name matching joins, these are displayed using a different color.
Thu Apr 10, 2008 5:32 pm View user's profile Send private message
judahr



Joined: 09 Mar 2007
Posts: 319
Country: United States

Post Reply with quote
SysOp wrote:
Please disregard the previous post. From older posts it looks like you are using 3.1. Version 3.1 should be already doing all of that, even 3.0 could partially handle most of the described. When you select a "table" in the join, don't select a table on the second level, use the arrow right key to expand the level and then select the required join condition on the third level, the rest will be inserted automatically for you. It can handle both predefined joins (using foreign keys) and column name matching joins, these are displayed using a different color.


This would also support occasions where joins are not defined within the same database or between databases. In those occasions, no join suggestions are provided, but could possibly be inferred.
Mon Apr 14, 2008 7:29 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Sorry, we are not currently supporting cross-database joins. We are caching catalog data one database at a time and so join candidates from multiple databases are not available simultaneously.
Mon Apr 14, 2008 10:35 am View user's profile Send private message
judahr



Joined: 09 Mar 2007
Posts: 319
Country: United States

Post Reply with quote
SysOp wrote:
Sorry, we are not currently supporting cross-database joins. We are caching catalog data one database at a time and so join candidates from multiple databases are not available simultaneously.


If you were already at:

Select
From dbo.Table1 t
INNER JOIN Database2.dbo.

Wouldn't you already have both database tables and columns cached? Then if you selected a column name from Database2.dbo.Table2, you would already know a specific column from Database2 and you would only have to search the columns in Table1 of the local database.
Mon Apr 14, 2008 11:20 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
No. Only the current database is cached. There are few exceptions when we go beyond the current database and pull the catalog data directly, for example, if you refer to a table in other database in the SELECT or WHERE clauses, but it doesn't work the same way for joins in SQL Server. For performance reasons, suggestions for JOINs come from the internal cache.

BTW, In Sybase, cross-database ref. constraints can be defined in tables in multiple databases and this info is stored in catalog tables just like same-database ref. constraints and available in the JOIN popups.
Mon Apr 14, 2008 12:01 pm View user's profile Send private message
judahr



Joined: 09 Mar 2007
Posts: 319
Country: United States

Post Reply with quote
SysOp wrote:
No. Only the current database is cached. There are few exceptions when we go beyond the current database and pull the catalog data directly, for example, if you refer to a table in other database in the SELECT or WHERE clauses, but it doesn't work the same way for joins in SQL Server. For performance reasons, suggestions for JOINs come from the internal cache.


Using the example above, you would only have to look at the local database for a column of the same name as the specific column chosen by the user.

I think we have a disconnect. I'm not saying you should popup join suggestions. The suggestion is centered on if a user goes down to a column name from a list of tables in a join clause (outside of the join suggestions), you use the column name.

Currently Column1 under Table2 from here (in the table list):

Select * from dbo.Table1 t INNER JOIN dbo.

Results in:
Select * from dbo.Table1 t INNER JOIN dbo.Table2.Column1


I was suggesting outputting something like this instead:

Select * from dbo.Table1 t INNER JOIN dbo.Table2 ON t2.Column1

This would allow proper use of the user selection. Then at this point, you could extend it using some of the suggestions I mentioned earlier.
Mon Apr 14, 2008 1:47 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Perhaps, I'm missing something. The provided example is not using a cross-database join. Are you suggesting, expanding a column selection in a join list to a JOIN [table] [alias] ON [alias].[selected column] text? In other words, if a table-level is expanded in the object list displayed after join and a column is selected., then convert this into [table] ON [column] rather than [table].[column]. If I get it right, it can be surely done.
Tue Apr 15, 2008 6:56 am View user's profile Send private message
judahr



Joined: 09 Mar 2007
Posts: 319
Country: United States

Post Reply with quote
SysOp wrote:
In other words, if a table-level is expanded in the object list displayed after join and a column is selected., then convert this into [table] ON [column] rather than [table].[column]. If I get it right, it can be surely done.


Yes. Exactly. Then I was taking that an additional step if possible. Since you have the other tables in the from clause you could see if that chosen column name has a defined join to the other tables or the same column name to automatically output the "= t2.Column" portion of the ON. However, one possible issue is if there is a lot of tables in the from clause. Searching them might be a hit. A solution to that would be to only search the previous table in the From clause.
Tue Apr 15, 2008 7:22 am View user's profile Send private message
judahr



Joined: 09 Mar 2007
Posts: 319
Country: United States

Post Selectable Joins Header Reply with quote
v. 3.5.11

When you have
Code:
Select * from dbo.Table t INNER JOIN


When possible, it pops open with a table with --joins as a suffix. I wish I could select that table as an option. It would be great for self joins or if you need the table twice for whatever reason.
Thu May 08, 2008 11:46 am View user's profile Send private message
Display posts from previous:    
This topic is locked: you cannot edit posts or make replies.    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Goto page Previous  1, 2, 3, ... 10, 11, 12  Next
Page 2 of 12

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.