Candidate lists involving Schemas - Bug and Feature Request

rldawgrldawg Posts: 33 Bronze 3
edited November 19, 2008 6:35AM in SQL Prompt Previous Versions
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.

Comments

  • Hi there,

    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
  • rldawgrldawg Posts: 33 Bronze 3
    Thanks Tom...but what you are saying to me is that I would need to use qualifed column names 100% of the time when really it is only mandatory when specifying a JOIN condtion. I would need to clutter my column list in a select statement just to get the JOIN condition to be properly formatted.

    I personally don't think SQL prompt should ever suggest SQL code that won't syntactically work.
  • Hi,

    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
Sign In or Register to comment.