OpenQuery 结果中的 SQL 语句出现“无效的列名”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/46354/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
"Invalid column name" error on SQL statement from OpenQuery results
提问by dmo
I'm trying to perform a SQL query through a linked SSAS server. The initial query works fine:
我正在尝试通过链接的 SSAS 服务器执行 SQL 查询。初始查询工作正常：
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
http://blogs.x2line.com/al/archive/2007/06/30/3187.aspx
The order of evaluation in MSSQL is:
MSSQL 中的求值顺序是：
- FROM
- ON
- JOIN
- WHERE
- GROUP BY
- HAVING
- SELECT
- ORDER BY
- 从
- 在
- 加入
- 在哪里
- 通过...分组
- 有
- 选择
- 订购者
So the alias wasn't processed until after the WHERE and HAVING clauses.
所以别名直到 WHERE 和 HAVING 子句之后才被处理。
采纳答案by Chris Miller
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.
不是 Value 是保留字，问题在于它是列别名，而不是列名。通过使其成为内联视图，“Value”成为列名，然后可以在 where 子句中使用。
回答by Andrew
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:
您使用“值”作为列别名，我认为该别名不能出现在 where 子句中。它只是用来命名返回的列值。您的 where 子句应引用原始列名称：
SELECT "Ugly OLAP name" as "Value"
FROM OpenQuery( OLAP, 'OLAP Query')
WHERE "Ugly OLAP name" > 0
回答by John Dunagan
I can vouch for leaving it out of GROUP BY. Good news is, it works just fine being a plain old selected alias.
我可以保证将其排除在 GROUP BY 之外。好消息是，作为一个普通的旧选择别名，它工作得很好。
回答by Till
Oh, bummer. I just saw, you select AS FOO. Don't you need a HAVING claus in this case?
哦，无赖。我刚看到，你选择AS FOO。在这种情况下，您不需要 HAVING 子句吗？
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!
我仍然不会使用“价值”。但可以肯定的是，请在您的文档中查找！
