“无效的列名"来自 OpenQuery 结果的 SQL 语句错误 [英] "Invalid column name" error on SQL statement from OpenQuery results

问题描述

我正在尝试通过链接的 SSAS 服务器执行 SQL 查询.初始查询工作正常:

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')

但是如果我尝试添加:

WHERE "Value" > 0

出现错误

无效的列名值"

知道我可能做错了什么吗?

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/存档/2007/06/30/3187.aspx

MSSQL 中的求值顺序是:

The order of evaluation in MSSQL is:

1. 来自
2. 开启
3. 加入
4. 哪里
5. 分组依据
6. 拥有
7. 选择
8. 订购者

所以别名直到 WHERE 和 HAVING 子句之后才被处理.

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

推荐答案

这应该有效:

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

并不是 Value 是保留字，问题在于它是列别名，而不是列名.通过使其成为内联视图，Value"成为列名，然后可以在 where 子句中使用.

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.

这篇关于“无效的列名"来自 OpenQuery 结果的 SQL 语句错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！