Oracle错误-查询运行时选择了其中一列，但没有同时选择两列,抛出错误“ORA-00937：不是单组组函数”

gxwragnw 于 2023-06-29 发布在 Oracle

关注(0)|答案(2)|浏览(101)

我正在经历的行为，我相信是一个错误，在甲骨文和我不知道该怎么办。
可以使用以下查询在Oracle Database 21 c Express Edition Release 21.0.0.0.0 - Production - Version 21.3.0.0.0上复制它：

select json_object(
    key 'foo' value json_arrayagg((select * from dual)),
    key 'nested' value (select json_object(key 'bar' value 2) from dual)
)
from dual;

运行此查询将抛出以下错误，其中position 133将光标放置在单词'bar'的开头：
[42000][937] ORA-00937：不是单组群函数位置：133

EDIT：至于为什么不简化为直接的json_object()调用，真实的的查询生成会产生这样的查询：

key 'aggregates' value (
    select json_object(key 'min' value min("TOTAL"))
    from (
      select "INVOICE".*
      from "INVOICE"
      where "INVOICE"."BILLINGCOUNTRY" = 'Canada'
      order by
        "INVOICE"."BILLINGADDRESS" asc nulls last,
        "INVOICE"."INVOICEID" asc nulls last
      offset 0 rows
      fetch next 30 rows only
    ) "INVOICE"
  ) format json

但是，如果注解掉任意一个键/值对，查询将运行：

如果从select语句中展开嵌套的json_object，它也会运行：

oracle

来源：https://stackoverflow.com/questions/76526290/oracle-bug-query-runs-with-either-of-the-columns-selected-but-not-both-throws

2条答案

按热度按时间

yhqotfr81#

您可以将查询简化为：

select json_object(
         key 'foo' value (select json_arrayagg(dummy) from dual),
         key 'nested' value (select json_object(key 'bar' value 2) from dual)
       ) AS json
from   dual;

然后到：

select json_object(
         key 'foo' value (select json_arrayagg(dummy) from dual),
         key 'nested' value json_object(key 'bar' value 2)
       ) AS json
from   dual;

然后进一步：

select json_object(
         key 'foo' value json_arrayagg(dummy),
         key 'nested' value json_object(key 'bar' value 2)
       ) AS json
from   dual;

其中所有输出：
| JSON |
| ------------ |
| {"foo":["X"],"nested":{"bar":2}} |
fiddle
至于您的更新，您可以将JSON_OBJECT移出子查询并返回最小值：

SELECT JSON_OBJECT(
         key   'aggregates'
         value JSON_OBJECT(
           key   'min'
           value ( select MIN(TOTAL)
                   from   ( select TOTAL
                            from   INVOICE
                            where  BILLINGCOUNTRY = 'Canada'
                            order by
                                   BILLINGADDRESS asc nulls last,
                                   INVOICEID asc nulls last
                            offset 0 rows
                            fetch next 30 rows only
                          )
                 )
         )
       ) AS json
FROM   DUAL;

其中，对于样本数据：

CREATE TABLE invoice (total, billingcountry, billingaddress, invoiceid) AS
SELECT DBMS_RANDOM.VALUE(110,120), 'Canada', 'ABC', LEVEL FROM DUAL CONNECT BY LEVEL <= 10 UNION ALL
SELECT 100, 'Canada', 'ABC', 11 FROM DUAL UNION ALL
SELECT DBMS_RANDOM.VALUE(110,120), 'Canada', 'ABC', LEVEL + 11 FROM DUAL CONNECT BY LEVEL <= 29 UNION ALL
SELECT 90, 'Canada', 'ABC', 41 FROM DUAL;

输出：
| JSON |
| ------------ |
| {"aggregates":{"min":100}} |
fiddle

赞(0）回复(0）举报 2023-06-29

gab6jxml2#

如果将来有人遇到这种情况，同事发现的解决方法是将json_arrayagg()移动到产生json_object()的子查询中：

with INVOICE_DATA(INVOICEID, BILLINGCOUNTRY, TOTAL) as (
    SELECT 1 as INVOICEID, 'Canada' as BILLINGCOUNTRY, 10 as TOTAL FROM DUAL UNION ALL
    SELECT 2 as INVOICEID, 'Canada' as BILLINGCOUNTRY, 20 as TOTAL FROM DUAL UNION ALL
    SELECT 3 as INVOICEID, 'Canada' as BILLINGCOUNTRY, 30 as TOTAL FROM DUAL
)

select json_object(
  -- Removed json_arrayagg("j") here
  key 'rows' value "j" format json,
  key 'aggregates' value (
    select json_object(key 'min' value min("TOTAL"))
    from (
      select "INVOICE_DATA".*
      from "INVOICE_DATA"
    ) "INVOICE"
  ) format json
  returning clob
) "data"
from (
  --- json_arrayagg call moved to wrap "j" directly here
  select json_arrayagg(json_object(
    key 'InvoiceId' value "INVOICEID",
    key 'BillingCountry' value "BILLINGCOUNTRY"
    returning clob
  )) "j"
  from (
    select "INVOICE_DATA".*
    from "INVOICE_DATA"
  ) "INVOICE"
) "alias_70049180"

输出：
| data |
| ------------ |
| {"rows":[{"InvoiceId":1,"BillingCountry":"Canada"},{"InvoiceId":2,"BillingCountry":"Canada"},{"InvoiceId":3,"BillingCountry":"Canada"}],"aggregates":{"min":10}} |

赞(0）回复(0）举报 2023-06-29

我来回答

Oracle错误-查询运行时选择了其中一列，但没有同时选择两列,抛出错误“ORA-00937：不是单组组函数”

2条答案

相关问题

热门标签

最新问答