CODEWITHSUNDEEP
sqlsql-servert-sql
UserSN
UserSN

Reputation: 1013

SQL Server: Invalid Column name from a SubQuery using XML

I have the following query that has a sub query giving me the Categories column. When I try to add the WHERE clause, I get an Invalid column name 'Categories' error.

SELECT 
    l.LID, 
    Company, 
    Doors, 
    City, 
    Region, 
    Country, 
    Categories = STUFF((
            SELECT 
                CONVERT(varchar(100), Junc_CatID) + ', ' 
            FROM BND_ListingJunction_testing j 
            WHERE j.Junc_LID = l.LID 
            FOR XML PATH('')), 1, 2, '')
FROM BND_Listing_testing l
--FILTERS
WHERE 
    (Categories = '[querystring:filter-Category]' or '[querystring:filter-Category]'='All')
GROUP BY 
    LID, 
    Company, 
    Doors, 
    City, 
    Region, 
    Country

Upvotes: 0

Views: 959

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

You can't use a created column if just created.

For example this is wrong, because taxes doesn't exist

SELECT 
    id, 
    sales, 
    sales * tax as taxes
FROM sales
WHERE 
    taxes > 100

So you need us a subquery or repeat the code.

SELECT *
FROM (SELECT id, sales, sales * tax as taxes FROM sales) T
WHERE 
    T.taxes > 100

OR 

SELECT 
    id, 
    sales, sales * tax as taxes
FROM sales
WHERE 
    sales * tax > 100

Upvotes: 1

Related Questions