Sql – “Invalid column name” error on SQL statement from OpenQuery results

sqlsql server

I'm trying to perform a SQL query through a linked SSAS server. The initial query works fine:

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

But if I try to add:

WHERE "Value" > 0

I get an error

Invalid column name 'Value'

Any ideas what I might be doing wrong?

So the problem was that the order in which elements of the query are processed are different that the order they are written. According to this source:

http://blogs.x2line.com/al/archive/2007/06/30/3187.aspx

The order of evaluation in MSSQL is:

  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. HAVING
  7. SELECT
  8. ORDER BY

So the alias wasn't processed until after the WHERE and HAVING clauses.

Best Answer

This should work:

SELECT A.Value
FROM (
SELECT "Ugly OLAP name" as "Value" 
FROM OpenQuery( OLAP, 'OLAP Query')
) AS a
WHERE a.Value > 0

It's not that Value is a reserved word, the problem is that it's a column alias, not the column name. By making it an inline view, "Value" becomes the column name and can then be used in a where clause.

Related Topic