ORACLE SQL LISTAGG未返回预期结果 [英] ORACLE SQL LISTAGG not returning expected result
问题描述
我正在运行Oracle Database 11g企业版11.2.0.4.0，PL/SQL版本11.2.0.4.0，并且在获取LISTAGG函数返回期望的数据集时遇到一些问题.
I am running Oracle Database 11g Enterprise 11.2.0.4.0, PL/SQL Release 11.2.0.4.0 and I am having some some issues getting the LISTAGG function to return the dataset I am expecting.
这是场景:
我有一个像这样的数据集
I have a data set that looks like this
我想在一行的一列中返回MOD_CODE列中的值，例如:
I want to return the values in the MOD_CODE column in a one column on one row e.g.:
AR4001,AR4002
我一直在尝试使用LISTAGG函数来完成此操作，例如:
I have been trying to complete this using the LISTAGG function e.g:
SELECT LISTAGG(MOD_CODE,',') WITHIN GROUP (ORDER BY MOD_CODE)
FROM XOTEST_A
WHERE MOD_CODE IN ('AR4001','AR4002')
但是它不返回任何数据(请参见屏幕截图)
But it is not returning any data (see screenshot)
我做错了什么?这是更广泛场景的一部分，因此我理想情况下想使用LISTAGG或类似的东西(我尝试使用wm_concat函数，但是它返回的HUGECLOB数据类型也无法正常工作)
What am I doing wrong? This is part of a wider scenario so I ideally want to use LISTAGG or similar (I tried using the wm_concat function, but its returning HUGECLOB data type and also not working)
我已经编写了SQL以构建用于复制的方案
I have written the SQL to build the scenario for repro
-- CREATE TABLE
CREATE TABLE XOTEST_A (
MOD_CODE NVARCHAR2(12),
DOM_CODE NVARCHAR2(12),
MOD_SNAM NVARCHAR2(15),
MOD_NAME NVARCHAR2(120),
SCH_CODE NVARCHAR2(6),
LEV_CODE NVARCHAR2(6),
PRS_CODE NVARCHAR2(12),
MOT_CODE NVARCHAR2(6),
MOD_CRDT NUMBER(5,2),
MOD_HOURS NUMBER(4),
MAP_CODE NVARCHAR2(12),
MOD_EREF NVARCHAR2(12),
MKS_CODE NVARCHAR2(6),
FPT_CODE NVARCHAR2(12),
ESB_CODE NVARCHAR2(6),
MOD_NPER NUMBER(2),
MOD_MOAS NVARCHAR2(1),
MOD_PMOA NVARCHAR2(1),
MEP_CODE NVARCHAR2(6),
DPT_CODE NVARCHAR2(12),
ELV_CODE NVARCHAR2(3),
ECL_CODE NVARCHAR2(3),
MOD_UTYC NVARCHAR2(3),
MOD_COGC NVARCHAR2(6),
MOD_STAT VARCHAR2(1 BYTE),
MOD_UPDD DATE,
MOD_KEYW NVARCHAR2(100),
MOD_IUSE VARCHAR2(1 BYTE),
MOD_TOCC NVARCHAR2(12),
MOD_DVNC NVARCHAR2(12),
MOD_UDF1 NVARCHAR2(15),
MOD_UDF2 NVARCHAR2(15),
MOD_UDF3 NVARCHAR2(15),
MOD_UDF4 NVARCHAR2(15),
MOD_UDF5 NVARCHAR2(15),
MOD_UDF6 NVARCHAR2(15),
MOD_UDF7 NVARCHAR2(15),
MOD_UDF8 NVARCHAR2(15),
MOD_UDF9 NVARCHAR2(15),
MOD_UDFA NVARCHAR2(15),
MOD_UDFB NVARCHAR2(15),
MOD_UDFC NVARCHAR2(15),
MOD_UDFD NVARCHAR2(15),
MOD_UDFE NVARCHAR2(15),
MOD_UDFF NVARCHAR2(15),
MOD_UDFG NVARCHAR2(15),
MOD_UDFH NVARCHAR2(15),
MOD_UDFI NVARCHAR2(15),
MOD_UDFJ NVARCHAR2(100),
MOD_UDFK NVARCHAR2(100),
MOD_VALC NVARCHAR2(12),
MOD_APRT NUMBER(5,2),
MOD_LANG NVARCHAR2(1),
MOD_ERFM NVARCHAR2(1),
MOD_MUSE VARCHAR2(1 BYTE),
MOD_FACC NVARCHAR2(6),
MOD_ISGX VARCHAR2(1 BYTE),
MOD_REGM NVARCHAR2(1),
MOD_FRID VARCHAR2(1 BYTE),
MOD_WEEI NVARCHAR2(1),
MOD_MODE NVARCHAR2(1)--,
--MOD_NOTE NCLOB
)
--INSERT VALUES INTO THE TABLE
INSERT INTO XOTEST_A (MOD_CODE,DOM_CODE,MOD_SNAM,MOD_NAME,SCH_CODE,LEV_CODE,PRS_CODE,MOT_CODE,MOD_CRDT,MOD_HOURS,MAP_CODE,MOD_EREF,MKS_CODE,FPT_CODE,ESB_CODE,MOD_NPER,MOD_MOAS,MOD_PMOA,MEP_CODE,DPT_CODE,ELV_CODE,ECL_CODE,MOD_UTYC,MOD_COGC,MOD_STAT,MOD_UPDD,MOD_KEYW,MOD_IUSE,MOD_TOCC,MOD_DVNC,MOD_UDF1,MOD_UDF2,MOD_UDF3,MOD_UDF4,MOD_UDF5,MOD_UDF6,MOD_UDF7,MOD_UDF8,MOD_UDF9,MOD_UDFA,MOD_UDFB,MOD_UDFC,MOD_UDFD,MOD_UDFE,MOD_UDFF,MOD_UDFG,MOD_UDFH,MOD_UDFI,MOD_UDFJ,MOD_UDFK,MOD_VALC,MOD_APRT,MOD_LANG,MOD_ERFM,MOD_MUSE,MOD_FACC,MOD_ISGX,MOD_REGM,MOD_FRID,MOD_WEEI,MOD_MODE/*,MOD_NOTE*/)
VALUES ('AR4001', 'SEN', 'AR4001', 'DESIGN STUDIO 1A', 'UL', '1', 'HASGRA1', 'A', 15, NULL, 'AR4001', NULL, 'AMOD', NULL, NULL, 1, 'M', 'M', NULL, 'SENDES', NULL, NULL, 'F', NULL, NULL, NULL, NULL, 'Y', NULL, NULL, NULL, '15/05/2008', 'V', 'P', NULL, 'N', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'N', NULL, NULL, NULL, NULL, NULL, NULL/*, ''*/)
COMMIT;
INSERT INTO XOTEST_A (MOD_CODE,DOM_CODE,MOD_SNAM,MOD_NAME,SCH_CODE,LEV_CODE,PRS_CODE,MOT_CODE,MOD_CRDT,MOD_HOURS,MAP_CODE,MOD_EREF,MKS_CODE,FPT_CODE,ESB_CODE,MOD_NPER,MOD_MOAS,MOD_PMOA,MEP_CODE,DPT_CODE,ELV_CODE,ECL_CODE,MOD_UTYC,MOD_COGC,MOD_STAT,MOD_UPDD,MOD_KEYW,MOD_IUSE,MOD_TOCC,MOD_DVNC,MOD_UDF1,MOD_UDF2,MOD_UDF3,MOD_UDF4,MOD_UDF5,MOD_UDF6,MOD_UDF7,MOD_UDF8,MOD_UDF9,MOD_UDFA,MOD_UDFB,MOD_UDFC,MOD_UDFD,MOD_UDFE,MOD_UDFF,MOD_UDFG,MOD_UDFH,MOD_UDFI,MOD_UDFJ,MOD_UDFK,MOD_VALC,MOD_APRT,MOD_LANG,MOD_ERFM,MOD_MUSE,MOD_FACC,MOD_ISGX,MOD_REGM,MOD_FRID,MOD_WEEI,MOD_MODE/*,MOD_NOTE*/)
VALUES ('AR4002', 'SEN', 'AR4002', 'DESIGN STUDIO 1A', 'UL', '1', 'HASGRA1', 'A', 15, NULL, 'AR4002', NULL, 'AMOD', NULL, NULL, 1, 'M', 'M', NULL, 'SENDES', NULL, NULL, 'F', NULL, NULL, NULL, NULL, 'Y', NULL, NULL, NULL, '15/05/2008', 'V', 'P', NULL, 'N', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'N', NULL, NULL, NULL, NULL, NULL, NULL/*, ''*/)
COMMIT;
-- NOW RUN THE SELECT STATEMENT
SELECT LISTAGG(MOD_CODE,',') WITHIN GROUP (ORDER BY MOD_CODE) LISTAGG_OUTPUT
FROM XOTEST_A
WHERE MOD_CODE IN ('AR4001','AR4002')
推荐答案
这似乎与错误19461687有关，并且此先前的问题.如果您将查询中的汇总值转储到11gR2或12cR1中，则会看到:
This seems to be related to bug 19461687, and this previous question. If you dump the aggregated value from your query in 11gR2 or 12cR1 you see:
LISTAGG_OUTPUT
--------------------------------------------------------------------------------------------------
Typ=1 Len=25 CharacterSet=AL32UTF8: 0,41,0,52,0,34,0,30,0,30,0,31,2c,0,41,0,52,0,34,0,30,0,30,0,32
在SQL * Plus和SQL Developer中，实际值显示为:
In SQL*Plus and SQL Developer the actual value is displayed as:
LISTAGG_OUTPUT
----------------------------------------
A R 4 0 0 1, A R 4 0 0 2
，您将无法从SQL Developer复制该值. (在12cR2中，零不再出现在转储中，显示的值没有空格，您可以复制它，因此该错误似乎已得到解决.)
and you can't copy the value from SQL Developer. (In 12cR2 the zeros no longer appear in the dump, the value is displayed without the spacing, and you can copy it, so the bug seems to have been fixed.)
这些空字节似乎导致Toad根本不显示该值，可能是因为它看到了第一个空字节并将其视为字符串终止符(或者无论如何都沿字符串).
Those null bytes seem to be causing Toad to not display the value at all, presumably because it sees the first null byte and treats it as a string terminator (or something along those lines anyway).
SQL Fiddle似乎可以解决这个问题，但dbfiddle似乎也有问题，当存在该查询时，整个Fiddle不会返回任何内容.
SQL Fiddle seems to cope with this, but db<>fiddle also seems to have a problem with it, and doesn't return anything for the whole fiddle when that query is present.
您可以将表列重新定义为varchar2而不是nvarchar2，但是由于某种原因，我认为它是该数据类型，因此可能不切实际.
You could redefine your table column as varchar2 instead of nvarchar2, but I assume it is that data type for a reason, so that probably isn't practical.
因此，您可以将其强制转换为查询的一部分:
So you could cast it as part of the query instead:
SELECT LISTAGG(CAST(MOD_CODE AS VARCHAR2(12)),',')
WITHIN GROUP (ORDER BY MOD_CODE) LISTAGG_OUTPUT
FROM XOTEST_A
WHERE MOD_CODE IN ('AR4001','AR4002');
LISTAGG_OUTPUT
----------------------------------------
AR4001,AR4002
或者查看漏洞19461687的补丁是否为您解决了该问题.
Or see if the patch for bug 19461687 fixes the problem for you.
这篇关于ORACLE SQL LISTAGG未返回预期结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
