Invalid column name in SQL Server

I'm trying to add where condition to my select statement but I'm getting invalid column name exception.

SELECT "Ugly OLAP name" as "Value" 
FROM OpenQuery( OLAP, 'OLAP Query')

But if I try to add:

WHERE "Value" > 0

you suggested that I have to use the original name and it works fine.

But what if I can't use the original column name as follow

SELECT 
    ROW_NUMBER() OVER(PARTITION BY P.ProviderID 
                      ORDER BY T.PostedUTC DESC, T.TransactionID DESC) as RN 
FROM
   Provider p 
INNER JOIN 
   Transaction T 
WHERE 
   RN = 1

How can I access RN a开发者_开发问答t my where ???

You need to use a CTE or a derived table:

SELECT *
FROM (  SELECT ROW_NUMBER() OVER(PARTITION BY P.ProviderID 
                                 ORDER BY T.PostedUTC DESC, T.TransactionID DESC) as RN,
               [More Columns]
        FROM Provider p 
        INNER JOIN Transaction T 
        ON SomeCondition) DT
where DT.RN = 1

Or

;WITH CTE AS
(
    SELECT ROW_NUMBER() OVER(PARTITION BY P.ProviderID 
                             ORDER BY T.PostedUTC DESC, T.TransactionID DESC) as RN,
           [More Columns]
    FROM Provider p 
    INNER JOIN Transaction T 
    ON SomeCondition
)
SELECT *
FROM CTE
where RN = 1

You could do it this way:

WITH T AS (
  SELECT ROW_NUMBER() OVER(PARTITION BY P.ProviderID ORDER BY T.PostedUTC DESC, T.TransactionID DESC) as RN 
  From Provider p Inner join Transaction T 
)
SELECT RN
FROM T
WHERE RN > 0;

EDIT: Missed second query in the question...

you must repeat the original calculation

Another way to make this easier to understand is to rewrite your query as a series of CTE's - Common Table Expressions. They look and act like 'mini local views' where you can rename columns, etc. It's tough from the example you gave but often you can rewrite complex queries and return to columns with nicer names in subsequent queries.