ORA-00979不是按表达式分组 [英] ORA-00979 not a group by expression

查看:84
本文介绍了ORA-00979不是按表达式分组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我通过以下查询获取ORA-00979:

I am getting ORA-00979 with the following query:

SELECT cr.review_sk, cr.cs_sk, cr.full_name,
tolist(to_char(cf.fact_date, 'mm/dd/yyyy')) "appt",
cs.cs_id, cr.tracking_number
from review cr, cs, fact cf
where cr.cs_sk = cs.cs_sk
and UPPER(cs.cs_id) like '%' || UPPER(i_cs_id) || '%'
and row_delete_date_time is null
and cr.review_sk = cf.review_wk (+)
and cr.fact_type_code (+) = 183050
GROUP BY cr.review_sk, cr.cs_sk, cf.fact_date, cr.tracking_number
ORDER BY cs.cs_id, cr.full_name;

我找不到在同一查询中同时具有GROUP BY和ORDER BY子句的任何示例.我尝试一次从组中删除每个字段,但仍然遇到相同的错误.

I couldn't find any examples that had both GROUP BY and ORDER BY clauses in the same query. I tried removing each field from the group by one at a time, but am still getting the same error.

推荐答案

必须将SELECT的所有列放在GROUP BY中,或在其上使用将结果压缩为单个值的函数(例如MINMAXSUM).

You must put all columns of the SELECT in the GROUP BY or use functions on them which compress the results to a single value (like MIN, MAX or SUM).

一个简单的例子来理解为什么会发生这种情况:假设您有一个这样的数据库:

A simple example to understand why this happens: Imagine you have a database like this:

FOO BAR
0   A
0   B

,然后运行SELECT * FROM table GROUP BY foo.这意味着数据库必须返回一行,并且第一列0满足GROUP BY的要求,但是现在有两个bar值可供选择.您期望哪个结果-AB?还是数据库应该返回多个行,从而违反GROUP BY的约定?

and you run SELECT * FROM table GROUP BY foo. This means the database must return a single row as result with the first column 0 to fulfill the GROUP BY but there are now two values of bar to chose from. Which result would you expect - A or B? Or should the database return more than one row, violating the contract of GROUP BY?

这篇关于ORA-00979不是按表达式分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆