VALUES
Correctly qualify the Table Value Constructor.
I frequently use a TVC to find the Max value among several values on a single row, as with the following:
;WITH MyCte AS (SELECT 1 AS a, 2 AS b, 3 AS c)
SELECT
MyCte.a,
MyCte.b,
MyCte.c,
HighestNumber = (SELECT MAX(Value.Val) FROM (VALUES (MyCte.a), (MyCte.b), (MyCte.c)) AS Value(Val))
FROM MyCte
When I qualify object names within that script, it incorrectly tries to add the 'Value' as a qualifier within the constructor:
;WITH MyCte AS (SELECT 1 AS a, 2 AS b, 3 AS c)
SELECT
MyCte.a,
MyCte.b,
MyCte.c,
HighestNumber = (SELECT MAX(Value.Val) FROM (VALUES (MyCte.a), (MyCte.b), (MyCte.c)) AS Value(Value.Val))
FROM MyCte
![](https://secure.gravatar.com/avatar/b8d607fe93bcc32a7cfb4ec640237363?size=40&default=https%3A%2F%2Fassets.uvcdn.com%2Fpkg%2Fadmin%2Ficons%2Fuser_70-6bcf9e08938533adb9bac95c3e487cb2a6d4a32f890ca6fdc82e3072e0ea0368.png)
Thanks for letting us know about this issue. It should now be fixed in the latest build of SQL Prompt (7.1.0.314)
-
Andrew Higgins commented
That is UNBELIEVABLE customer support. Redgate has seriously impressed me by doing this.
I've tested the release, and it now works correctly.