Comment utiliser COLLECT avec VARCHAR2 Oracle 10g

Question

J'essaie de faire en sorte que la fonction COLLECT fonctionne pour moi. J'utilise 10g et j'ai donc constaté que LISTAGG et WM_CONCAT ne fonctionnent pas (erreurs d'identifiants invalides). Les données que j'ai sont par exemple les suivantes.

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

Ce que j'ai besoin de retourner est le suivant

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

En utilisant ce qui suit, j'obtiens l'erreur : TO_STRING est un identifiant invalide

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

En utilisant ce qui suit, j'obtiens l'erreur : expected CHAR "inconsistent datatypes : expected %s got %s".

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

En utilisant ce qui suit, j'obtiens l'erreur : expected NUMBER "inconsistent datatypes : expected %s got %s".

COLLECT(DISTINCT WHSE_LOT)

Y a-t-il un moyen de faire fonctionner cette fonction pour moi ?

Answer 1

El fonction de collecte crée une table imbriquée, dans votre cas une table de chaînes de caractères, que vous devez ensuite convertir en un type spécifique - c'est-à-dire un type défini comme une table de varchar2. Vous ne pouvez pas effectuer un cast vers une seule chaîne de caractères.

Il existe quelques listes bien connues de techniques d'agrégation de chaînes de caractères, comme celui-ci . Il y a un qui utilise la collecte mais vous avez toujours besoin du type de tableau et d'une fonction pour convertir le tableau généré en une chaîne de caractères délimitée.

Je copie cet exemple mot pour mot :

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;
/

Avec ce type et cette fonction, vous faites alors :

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

---

C'est intéressant, la version 10g de collect ne prend pas en charge DISTINCT ; il ne se plaint pas ( !?), mais laisse des doublons.

Vous pouvez passer la collection par la fonction set pour supprimer les doublons :

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

Démonstration rapide en 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