Candidate lists involving Schemas - Bug and Feature Request
rldawg
Posts: 35 Bronze 3
I have two comments on candidate lists involving schemas:
1. This is a BUG: When listing candidates for join conditions, the schema name does not preface the object name and this is faulty SQL.
If a table is called Sales.SalesPerson and another table is called Orders.OrderHeader...when writing a join SQL Prompt will suggest for an inner join condition
ON SalesPerson.SalespersonId = OrderHeader.SalespersonId
This is not correct. The schema name must preface the table name:
Sales.SalesPerson.SalespersonId = Orders.OrderHeader.SalespersonId
2. I would suggest that objects (tables, procs, views, functions etc...) that belong to any schema other than dbo not show up on the candidate list until the schema name is selected...since the object name is not useful without the schema name in front of it anyway.
If a table is named Billing.Invoices then typing in:
Select * from Invoices
will produce an error. So why show invoices in the candidate list until the schema name is selected?
Thanks for the consideration.
1. This is a BUG: When listing candidates for join conditions, the schema name does not preface the object name and this is faulty SQL.
If a table is called Sales.SalesPerson and another table is called Orders.OrderHeader...when writing a join SQL Prompt will suggest for an inner join condition
ON SalesPerson.SalespersonId = OrderHeader.SalespersonId
This is not correct. The schema name must preface the table name:
Sales.SalesPerson.SalespersonId = Orders.OrderHeader.SalespersonId
2. I would suggest that objects (tables, procs, views, functions etc...) that belong to any schema other than dbo not show up on the candidate list until the schema name is selected...since the object name is not useful without the schema name in front of it anyway.
If a table is named Billing.Invoices then typing in:
Select * from Invoices
will produce an error. So why show invoices in the candidate list until the schema name is selected?
Thanks for the consideration.
Comments
I'm sorry that you have run into this confusion. You can get exactly the behaviour you want by changing the SQL Prompt options. Simply open up the SQL Prompt options screen and go to the 'Inserted Candidates' tab. If you check the first 2 entries for 'Qualify object names' and 'Qualify column names' then SQL Prompt will work as you want it to.
Good luck!
Tom Harris
Red Gate
I personally don't think SQL prompt should ever suggest SQL code that won't syntactically work.
indeed. I may have jumped in a little hastily there. SQL Prompt cannot currently be configured to give fully qualified names in join conditions and table names, but not on inserted column names.
Apologies, Tom