[ACCEPTED]-Invalid column name" error on SQL statement from OpenQuery results-sql-server

Accepted answer
Score: 17

This should work:

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

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

Score: 6

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

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

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

SELECT whatever AS value FROM table HAVING value > 1;

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

Score: 0

I can vouch for leaving it out of GROUP 2 BY. Good news is, it works just fine being 1 a plain old selected alias.

More Related questions