ORA-00979不是具有复杂EXTRACTVALUE的GROUP BY表达式 [英] ORA-00979 Not a GROUP BY Expression with complex EXTRACTVALUE

问题描述

我正在尝试对带有XMLTYPE列的表执行一个相当简单的Oracle查询:

I'm trying to execute a rather simple Oracle query against a table with a XMLTYPE column:

Select POB_CODPOL, CODSIS From (
    Select T1.POB_CODPOL, EXTRACTVALUE(T1.POB_XMLPOL, '/Polbas/polfun[nomfun="filterBySystem"]/extpar[codele="codsis"]/valele/text()') CODSIS 
    From TDTX_POLITICA_CLOB T1
    Where T1.POB_CODEMP = '001840' 
)     
Group By POB_CODPOL, CODSIS

这抛出一个ORA-00979 Not a GROUP BY Expression，我不太了解. 更糟糕的是:当我执行完全相同的查询但使用简化的XPATH查询时，确实起作用:

This throws a ORA-00979 Not a GROUP BY Expression, which I don't really understand. Even worse: when I execute the exact same query, but with a simplified XPATH query does work:

Select POB_CODPOL, CODSIS From (
    Select T1.POB_CODPOL, EXTRACTVALUE(T1.POB_XMLPOL, 'Polbas/codpol/text()') CODSIS 
    From TDTX_POLITICA_CLOB T1
    Where T1.POB_CODEMP = '001840' 
)     
Group By POB_CODPOL, CODSIS

在使用GROUP BY时，Oracle似乎不喜欢像[nomfun="filterBySystem"]这样的条件(没有分组子句，一切正常).

It looks like Oracle doesn't like conditions like [nomfun="filterBySystem"] when using a GROUP BY (without the grouping clause, everything works fine).

关于为什么会发生这种情况的任何想法?

Any idea on why this can be happening?

编辑:内部查询的结果非常简单:

Edit: the result of the inner query is rather simple:

推荐答案

EXTRACTVALUE已弃用.

Oracle建议使用 XMLQUERY ， XMLTABLE .

Oracle recommends to use XMLQUERY, XMLTABLE for it.

这应该工作:

WITH t as
    (SELECT T1.POB_CODPOL, x.CODSIS 
     FROM TDTX_POLITICA_CLOB T1 
          NATURAL JOIN XMLTABLE('/Polbas/polfun[nomfun="filterBySystem"]/extpar[codele="codsis"]/valele‌​' 
             PASSING POB_XMLPOL COLUMNS 
             CODSIS VARCHAR2(50) PATH '/') x
     Where T1.POB_CODEMP = '001840')
SELECT POB_CODPOL, CODSIS 
FROM t
GROUP BY POB_CODPOL, CODSIS;

这篇关于ORA-00979不是具有复杂EXTRACTVALUE的GROUP BY表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！