将 Oracle 查询转换为 SQL Server - 错误 130

发布于 2024-12-16 12:51:48 字数 739 浏览 1 评论 0原文

我的查询是这样的：

select u1.LastName, u1.FirstName,
    sum(case when not exists(select 1
                     from CaseHistory ch2
                     where ch1.caseid = ch2.caseid
                     and ch2.ActiveDate = somedate
                     ch2.ID in (0, 2, 41))
then 1
else 0
end) as WorkedRecord
from sometables

此查询在 Oracle 中有效，但我们现在正在切换到 SQL Server。在 SQL Server 中，我们收到以下错误：

错误 130：无法对包含聚合或聚合的表达式执行聚合函数子查询。

这是我的解决方案：

我最终做了一个内联视图，然后对其进行总结。我做了这样的事情：选择总和（a.WorkedRecord）从（选择（不存在的情况（选择1 来自案例历史第 2 章... then 1 else 0 end) 作为 WorkedRecord) 来自 Sometables）a

这似乎有效。

原文

The query I have is this:

select u1.LastName, u1.FirstName,
    sum(case when not exists(select 1
                     from CaseHistory ch2
                     where ch1.caseid = ch2.caseid
                     and ch2.ActiveDate = somedate
                     ch2.ID in (0, 2, 41))
then 1
else 0
end) as WorkedRecord
from sometables

This query works in Oracle, but we're now changing over to SQL Server. In SQL Server we get the following error:

Error 130: Cannot perform an aggregate function on an expression containing an aggregate or
a subquery.

Here's my solution:

I ended up doing an in-line view and then summing that. I did something like this:
select sum(a.WorkedRecord)
from
(select
(case when not exists(select 1
from CaseHistory ch2...
then 1 else 0 end) as WorkedRecord)
from Sometables) a

This seems to work.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

等风来 2024-12-23 12:51:48

在这种情况下，您可以执行左连接，而不是子查询，然后对连接结果求和吗？

SELECT
    u1.LastName,
    u1.FirstName,
    SUM(CASE WHEN ch2.caseid IS NULL THEN 1 ELSE 0 END) AS WorkedRecord
FROM
    sometables s 
    LEFT JOIN CaseHistory ch2 ON
        ch2.caseid = ch1.caseid
        AND ch2.ActiveDate = somedate
        AND ch2.ID in (0, 2, 41)
GROUP BY
    ul.LastName,
    ul.FirstName

Instead of the sub-query... could you do a left join in this situation, and then total the results of the join?

SELECT
    u1.LastName,
    u1.FirstName,
    SUM(CASE WHEN ch2.caseid IS NULL THEN 1 ELSE 0 END) AS WorkedRecord
FROM
    sometables s 
    LEFT JOIN CaseHistory ch2 ON
        ch2.caseid = ch1.caseid
        AND ch2.ActiveDate = somedate
        AND ch2.ID in (0, 2, 41)
GROUP BY
    ul.LastName,
    ul.FirstName

回复收藏 0 原文

~没有更多了~