"Invalid column name" error on SQL statement from OpenQuery results

Question

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.

Answer 1

Sounds like "value" is a reserved word?

Answer 2

I would agree with Till. In SQL I have had to put the comun name in square brackets before. Ex: [Value] = ???

Answer 3

I've tried using different column names and tried enclosing the names in " " and [ ] to no avail.

Answer 4

Oh, bummer. I just saw, you select AS FOO. Don't you need a HAVING claus in this case?

SELECT whatever AS value FROM table HAVING value > 1;

I still would not use "value". But to be sure, look it up in your docs!

Answer 5

I changed "Value" to "Foo" and tried both:

WHERE "Foo" > 0

and

HAVING "Foo" > 0

I still get the error:

Invalid column name 'Foo'.

Answer 6

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.

Answer 7

@Chris Miller:

Thanks for the speedy answer. Do you happen to know why this is necessary?

Answer 8

You're using "Value" as a column alias, and I don't think the alias can appear in the where clause. It's simply used to name the returned column value. Your where clause should refer to the original column name:

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

Answer 9

@Andrew:

You're correct. Using the original column name works.

Answer 10

I am getting the error at the below statemnt.

Declare @LEFT_USERCONTROL_PARAM varchar(25) select @LEFT_USERCONTROL_PARAM = "'" + @clm_id + "','" + @svc_code + "','"+ convert(varchar, @seq_num) + "','" + @instance_id + "','" + @template_code + "'"

Is this not the correct format.

Pls sugest!..

Thanks @Bharath Reddy VasiReddy