oracle blob group,Oracle 12 - 分组列表中的Blob打破了listagg？(Oracle 12 - Blob in group-by column list breaks ...

最新推荐文章于 2023-03-25 13:34:17 发布

独处着匠人

最新推荐文章于 2023-03-25 13:34:17 发布

阅读量329

点赞数

文章标签： oracle blob group

在Oracle12c中，尝试在使用Listagg函数的分组查询中包含Blob列时遇到了错误。问题在于Blob列不能用于GROUP BY子句。一种解决方法是在进行连接操作前先对数据进行分组，然后根据分组后的结果进行查询。这样可以避免在GROUP BY中直接使用Blob列，从而解决不一致的数据类型错误。

摘要由CSDN通过智能技术生成

Oracle 12 - 分组列表中的Blob打破了listagg？(Oracle 12 - Blob in group-by column list breaks listagg?)

我们有一个表集合，我们需要从中创建一个视图...视图中有30个列。一个(-the-)列使用listagg聚合其中一个表中的多个值...另一个列是另一个表中的BLOB。

一切都很顺利，因为我一次创建了一列视图。添加listagg - 然后开始添加所有group-by列..当我添加BLOB列时，返回错误不一致的数据类型..期待 - 得到BLOB 。

所以，只是一个普遍的问题，...在listagg列的group-by列列表中有一个BLOB列会破坏整个事情吗？有没有办法只使用SQL来解决这个问题？

任何和所有的见解和建议表示赞赏。因为涉及多个表和许多列，所以我希望我能够很好地解释这种情况，以便在不尝试构建整个案例的情况下获得建议。

We have a collection of tables from which we need a view created... there are 30-some columns in the view. One (-the last-) column is using listagg to aggregate multiple values from one of the tables... another column is a BLOB from another table.

Everything was going well as I created the view one column at a time. adding the listagg--then began adding all the group-by columns.. when I added the BLOB column, error inconsistent data type returned.. expecting - got BLOB.

So, just a general question,... does having a BLOB column in the group-by list of columns for a listagg column break the whole thing? Is there a way to work around the issue using only SQL?

Any and all insights and suggestions appreciated. Because there are multiple tables and many columns involved, I am hoping I explained the situation well enough to get suggestions without trying to build the entire case for review..

原文：https://stackoverflow.com/questions/33570544

更新时间：2020-06-23 14:06

最满意答案

您不能按LOB列分组。如果您从一个表中获取listagg然后想要从第二个表中选择信息，为什么不在进行连接之前先进行分组？

例如。就像是：

select t1.col1,

t1.col2,

t1.grouped_col3,

t2.blob_col

from (select col1,

col2,

listagg(col3, ',') within group (order by col4) grouped_col3

from first_table

group by col1, col2) t1

inner join t2 on (t1.col1 = t2.col1 and t1.col2 = t2.col2);

You can't group by LOB columns. If you're getting the listagg from one table and then wanting to select information from a second table, why not do the grouping first before doing the join?

Eg. something like:

select t1.col1,

t1.col2,

t1.grouped_col3,

t2.blob_col

from (select col1,

col2,

listagg(col3, ',') within group (order by col4) grouped_col3

from first_table

group by col1, col2) t1

inner join t2 on (t1.col1 = t2.col1 and t1.col2 = t2.col2);

2015-11-06

相关问答

你在使用11.1还是11.2？ LISTAGG在11.2中引入，它在11.1中不可用。你的SQL语句在11.2中对我来说是有效的。但是你会在11.1中得到一个错误，而在11.1中ORA-00923看起来像是一个合理的错误。 Are you using 11.1 or 11.2? LISTAGG was introduced in 11.2, it was not available in 11.1. Your SQL statement looks valid to me in 11.2. B

...

你可以使用： SELECT LISTAGG('''' || student_name || '''',',')

WITHIN GROUP (ORDER BY student_name)

FROM students;

或使用ENQUOTE_LITERAL函数： SELECT LISTAGG(DBMS_ASSERT.ENQUOTE_LITERAL(student_name),',')

WITHIN GROUP (ORDER BY student_name) AS r

...

做两个级别的聚合： SELECT storeId,

LISTAGG(ProductCategory || ':' || '(' || ProductIds || ')', ', ')

WITHIN GROUP (ORDER BY ProductCategory) as ProductsAndCategories

FROM (SELECT StoreId, ProductCategory,

LISTAGG(ProductId, ',')

...

您可以使用子查询删除重复项： select department_id, count(*),

listagg(manager_id, ' | ') within group (order by manager_id)

from (select distinct department_id, manager_id

from employees

) e

group by department_id

You can use a subquery to remove

...

这似乎与bug 19461687和之前的问题有关。如果从11gR2或12cR1中的查询转储聚合值，您会看到： 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

...

看起来您使用的是旧版本的Toad，它无法识别Oracle 11gR2中引入的listagg函数。从Dell支持网站：描述语法检查程序无法识别11gR2新分析函数LISTAGG。 ... 上面的代码运行正常，但如果我们尝试格式化它，将出现以下错误消息： ERROR第4行，第10列，ending_line 4，ending_col 16，找到listagg，只有CUME_DIST，DENSE_RANK，PERCENT_RANK，RANK，PERCENTILE_CONT和PERCENTILE_D

...

像这样的东西： SELECT

a.AGMT_NUM,

p.STIP_TYPE_DESC,

s.EXST_FLAG,

( SELECT LISTAGG( t.RMK_TEXT, CHR(13) || CHR(10) )

WITHIN GROUP ( ORDER BY t.RMK_LINENO )

FROM GENERAL_REMARKS r

INNER JOIN GENERAL_REMARK_TEXT t

...

我解决了它，下面是脚本 Select

Col1, Col2, Col3, Col4,