如何在VARCHAR2 Oracle 10g中使用COLLECT [英] How to Use COLLECT with VARCHAR2 Oracle 10g

问题描述

我正在尝试让COLLECT函数为我工作.我使用的是10g，因此发现LISTAGG和WM_CONCAT无法正常工作(无效的标识符错误).我的数据例如如下.

I'm trying to get the COLLECT function to work for me. I'm using 10g and therefore found that LISTAGG and WM_CONCAT will not work (invalid identifier errors). The data I have is for example as follows.

Order  Lot
123    A23088
123    A23089
089    AABBCC
305    120848
305    CCDDYY

我需要返回的内容如下

Order   Lot
123     A23088, A23089
089     AABBCC
305     120848, CCDDYY

使用以下命令，我收到错误消息:TO_STRING是无效的标识符

Using the following, I get the error: TO_STRING is an invalid identifier

TO_STRING ( CAST(COLLECT(DISTINCT LOT) AS varchar2(100)) ) AS LOT

使用以下命令，我收到错误消息:预期的CHAR数据类型不一致:预期的％s得到了％s"

Using the following, I get the error: expected CHAR "inconsistent datatypes: expected %s got %s"

TO_CHAR ( CAST(COLLECT(DISTINCT LOT) AS varchar2(100)) ) AS LOT

使用以下命令，我得到错误:预期的NUMBER数据类型不一致:预期的％s得到了％s"

Using the following, I get the error: expected NUMBER "inconsistent datatypes: expected %s got %s"

COLLECT(DISTINCT WHSE_LOT)

有什么办法可以让我使用此功能?

Is there any way to get this function to work for me?

推荐答案

收集函数创建一个嵌套表(在您的情况下为一个字符串表)，然后将其转换为特定类型-即定义为varchar2表的类型.您不能转换为单个字符串.

The collect function creates a nested table, in your case a table of strings, which you would then cast to a specific type - that is, a type defined as a table of varchar2. You can't cast to a single string.

有一些字符串聚合技术的知名列表，例如 .有一个使用collect ，但是您仍然需要该表类型和将生成的表转换为定界字符串的函数.

There are some well-known lists of string aggregation techniques, like this one. There is one that uses collect, but you still need the table type and a function to convert the generated table to a delimited string.

逐字复制该示例:

CREATE OR REPLACE TYPE t_varchar2_tab AS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE FUNCTION tab_to_string (p_varchar2_tab  IN  t_varchar2_tab,
                                          p_delimiter     IN  VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS
  l_string     VARCHAR2(32767);
BEGIN
  FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP
    IF i != p_varchar2_tab.FIRST THEN
      l_string := l_string || p_delimiter;
    END IF;
    l_string := l_string || p_varchar2_tab(i);
  END LOOP;
  RETURN l_string;
END tab_to_string;
/

使用该类型和功能，您可以执行以下操作:

With that type and function you then do:

SELECT tab_to_string(CAST(COLLECT(DISTINCT lot) AS t_varchar2_tab)) AS lot FROM ...

---

有趣的是，收集的10g版本没有不支持DISTINCT;它不会抱怨(！?)，但是会重复.

---

Interestingly, the 10g version of collect doesn't support DISTINCT; it doesn't complain (!?), but leaves duplicates.

您可以通过集合函数删除重复项:

You can pass the collection through the set function to remove the duplicates:

SELECT tab_to_string(SET(CAST(COLLECT(DISTINCT lot) AS t_varchar2_tab))) AS lot FROM ...

在10.2.0.5中运行的快速演示:

Quick demo run in 10.2.0.5:

create table table1(order_no number, lot varchar2(10));

insert into table1 values (590288, '2016538');
insert into table1 values (590288, '2016535');
insert into table1 values (590288, '6016535');
insert into table1 values (590288, '2016535');
insert into table1 values (590288, '2016538');

SELECT order_no, tab_to_string(SET(CAST(COLLECT(DISTINCT lot) AS t_varchar2_tab))) AS LOT
FROM table1 WHERE order_no = 590288 GROUP BY order_no;

  ORDER_NO LOT                                              
---------- --------------------------------------------------
    590288 2016538,2016535,6016535                           

这篇关于如何在VARCHAR2 Oracle 10g中使用COLLECT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！