Setting incorrect alias
Rae
Posts: 3 New member
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)
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
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
Redgate Software
Technical Lead, SQL Monitor
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