Maximize the power of SQL Prompt with SQL Toolbelt Essentials. Watch now.

Setting incorrect alias

RaeRae Posts: 3 New member
edited March 20, 2017 6:04AM in SQL Prompt
Not sure if we've stumbled across a bug, or whether this is just a misunderstanding of settings? We're running SQL Prompt Version 7.4.1.603 and the two settings in question are:

Inserted Code > Qualification
[x] Qualify column names with aliases

Format > Styles > Actions
[x] Qualify object names

If both are checked and we format the attached code, it changes the ProductID in the IN() statement from having no alias to having the el alias - which rather changes what the script is doing!


IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.EventLog') AND type in (N'U'))
DROP TABLE dbo.EventLog;

CREATE TABLE dbo.EventLog
(
EventID INT IDENTITY(1, 1) NOT NULL
,PolicyID INT NOT NULL
,ProductID INT NULL
)
ON [PRIMARY];

GO

SELECT
el.EventID
,el.PolicyID
FROM
dbo.EventLog AS el
WHERE
el.ProductID IN (SELECT ProductID FROM ##Temp)

Comments

  • James RJames R Posts: 104 Silver 4
    Hi Rae,

    Thanks for your post. I've managed to reproduce this locally, and it seems like we're not checking qualification in temp tables correctly.

    Hopefully we can have a fix soon :)
    Thanks,
    James
    Software Engineer
    Redgate Software
  • Just a quick note -- we are tracking this bug under reference SP-6199.
    Michael Clark
    Technical Lead, SQL Monitor
  • Harry FrankishHarry Frankish Posts: 53 New member
    Hi Rae

    In this build SQL Prompt doesn't qualify columns which are ambiguous. This will stop SQL Prompt adding the 'el' alias to 'ProductID' in the IN() statement from your example.

    Please let us know if it fixes it for you or if you find any other issues.

    Cheers
    Harry
Sign In or Register to comment.