Options

SQL Prompt Wrongly Alias'ing order by clause

GregDoddGregDodd Posts: 12 Bronze 2
in SQL Prompt
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
Tagged:
· Share on Twitter

Answers

Sign In or Register to comment.