SQL Server: Invalid Column name from a SubQuery using XML

Question

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

Possible duplicate of [Reference alias (calculated in SELECT) in WHERE clause](http://stackoverflow.com/questions/11182339/reference-alias-calculated-in-select-in-where-clause) — ps2goat, Dec 05 '16 at 20:09
you can nest your current statement within another statement, then filter the external statement by the calculated column values above. `Select * from ( /* original select*/) x where x.Categories = /* original filters... */` — ps2goat, Dec 05 '16 at 20:30
@ps2goat in the original filters section i'm adding `'[querystring:filter-Category]' or '[querystring:filter-Category]'='All'` and that gives a syntax error by the `OR`. I will be adding addition AND clauses similar to the one above. — UserSN, Dec 05 '16 at 21:17
@AlexP - shouldn't you be testing the `Categories` column in both cases? or what does the actual query render to? — ps2goat, Dec 05 '16 at 22:24
@ps2goat it's because I have a select box that returns either a CATID into the querystring OR if nothing is selected in my dropdown then it passes the ALL paramter into querystring. So I want my query to listen for either a ID value or ALL — UserSN, Dec 05 '16 at 22:33

score 1 · Accepted Answer · edited Dec 05 '16 at 20:31

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

SQL Server: Invalid Column name from a SubQuery using XML

1 Answers1