CODEWITHSUNDEEP
c#sql-serversql-server-2008
ONYX
ONYX

Reputation: 5859

Invalid Column Name in 'Where' Expression

I found this SQL function that iterates over the table and it finds the ParentID the code works except when I put a where clause in it doesn't work on the newly created column. If I don't use the where clause it produces Expr1 as the root of the tree which is what I want but I can't put a condition on it.

Can someone help me fix this problem?

ALTER FUNCTION dbo.fn_Root
(
    @PostID int
)
RETURNS int
AS 
BEGIN
DECLARE @R int

SELECT @R = CASE WHEN ParentPostID = 0 THEN PostID
                ELSE dbo.fn_Root(ParentPostID)
            END
        FROM Post
        WHERE PostID = @PostID

RETURN @R
END


SELECT PostID, Title, dbo.fn_Root(PostID) AS Expr1
FROM Post WHERE Expr1 = 5

Upvotes: 2

Views: 249

Answers (2)

jklemmack
jklemmack

Reputation: 3636

You can't refer to a calculated / computed column alias directly in a WHERE clause in SQL Server. However, one trivial work-around is to wrap the statement and place your WHERE clause on the outside, referring to the computed column:

SELECT * FROM (
    SELECT PostID, Title, dbo.fn_Root(PostID) AS Expr1
    FROM Post)
WHERE Expr1 = 5

Upvotes: 3

Christian Specht
Christian Specht

Reputation: 36421

Another possible workaround: you can put the calculated value in the WHERE clause again:

SELECT PostID, Title, dbo.fn_Root(PostID) AS Expr1
FROM Post 
WHERE dbo.fn_Root(PostID) = 5

Upvotes: 2

Related Questions