How can we help you today? How can we help you today?

SQL Prompt Wrongly Alias'ing order by clause

When you have a table joined to itself and you alias the 2nd table but not the first, SQL Prompt formats the order by clause to use the Aliased tablename, overwriting the chosen filename.

e.g.:
SELECT Product.ProductCode
,MainProduct.ProductCode
FROM Product
INNER JOIN Product AS MainProduct
ON MainProduct.ProductItemID = Product.MainProductID
ORDER BY Product.ProductName

When you format, it becomes:

SELECT Product.ProductCode
,MainProduct.ProductCode
FROM Product
INNER JOIN Product AS MainProduct
ON MainProduct.ProductItemID = Product.MainProductID
ORDER BY MainProduct.ProductName

The field names in the select statement correctly pick which table they are coming from (the aliased or non-aliased), but the order by clause always picks the aliased table.

If I alias both tables then it doesn't have a problem (Even if the alias is the same as the table name):
SELECT Product.ProductCode
,MainProduct.ProductCode
FROM Product AS Product
INNER JOIN Product AS MainProduct
ON MainProduct.ProductItemID = Product.MainProductID
ORDER BY Product.ProductName
GregDodd
0

Comments

4 comments

Sort by
  • Tianjiao_Li
    Hi @GregDodd

    I'm not able to reproduce it with version 9.2.8.6358. Please upgrade and test again.
    Tianjiao_Li
    0
  • GregDodd
    I was on 9.2.6.6145, but it's still happening after updating to 9.2.8.6358. I can run the following on a new database (or tempdb):

    CREATE TABLE Product
    (
    ProductItemID INT PRIMARY KEY,
    ProductName VARCHAR(50),
    ProductCode VARCHAR(10),
    MainProductID INT
    )

    Then write the following script:

    SELECT Product.ProductCode
    ,MainProduct.ProductCode
    FROM Product
    INNER JOIN Product AS MainProduct
    ON MainProduct.ProductItemID = Product.MainProductID
    ORDER BY Product.ProductName

    Then when I format it I get MainProduct.ProductName in the order by clause
    GregDodd
    0
  • Tianjiao_Li
    Hi @GregDodd

    I've reproduced the issue! Thanks very much for your help.

    It's logged as SP-7229 in our internal bug tracking system. Please keep an eye on the release note for the fix!
    Tianjiao_Li
    0
  • Russell D
    This issue has been closed, and the workaround is to alias both table sources.
    Russell D
    0

Add comment

Please sign in to leave a comment.