Cadenas a filas con STRTOK_SPLIT_TO_TABLE()

La conversión de columnas de cadenas de datos separados por un ‘token’ en filas se ha tratado ya en este blog. Entonces se hizo utilizando recursividad (WITH RECURSIVE…), en esta ocasión vamos a ver cómo se puede hacer con la nueva función STRTOK_SPLIT_TO_TABLE() -disponible en Teradata desde la versión 14.0- y algunas particularidades que tiene su uso.

La función hace lo que dice su nombre: convierte en tablas cadenas delimitadas por ‘tokens’. Pero hace falta saber un poco más de ella: lo principal es que necesita un valor ‘key’ para referenciar las filas de la tabla resultante de la cadena de ‘tokens’ a partir de las filas de la tabla original. Esta clave funciona como una especie de PK/FK.

Las columnas de cadenas separadas por ‘tokens’ pueden ser tanto caracteres “normales” (CHAR, VARCHAR) como CLOB. La cadena ‘token’ (delimitador) puede ser de hasta 64 caracteres (el tipo es VARCHAR(64)), aunque normalmente será un único caracter (de hecho, su “DEFAULT” es el espacio simple (‘ ‘)).

Bueno, pues con todo esto vamos a crearnos la consabida tabla de ejemplos:

 BTEQ -- Enter your SQL request or BTEQ command:
CREATE MULTISET TABLE CARLOS.PRUEBA_08 ,
     NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      ID_N INTEGER NOT NULL,
      C_TXT CHAR(1) NOT NULL,
      V_TXT VARCHAR(25) NOT NULL )
PRIMARY INDEX ( ID_N )
;


 *** Table has been created.
 *** Total elapsed time was 1 second.


 BTEQ -- Enter your SQL request or BTEQ command:
INSERT INTO CARLOS.PRUEBA_08 VALUES (1,'A','001');


 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.


 BTEQ -- Enter your SQL request or BTEQ command:
INSERT INTO CARLOS.PRUEBA_08 VALUES (2,'B','002,003');


 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.


 BTEQ -- Enter your SQL request or BTEQ command:
INSERT INTO CARLOS.PRUEBA_08 VALUES (4,'D','004,005,006');


 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.


 BTEQ -- Enter your SQL request or BTEQ command:
SELECT * FROM CARLOS.PRUEBA_08
ORDER BY 1,2;


 *** Query completed. 3 rows found. 3 columns returned.
 *** Total elapsed time was 1 second.

       ID_N  C_TXT  V_TXT
-----------  -----  -------------------------
          1  A      001
          2  B      002,003
          4  D      004,005,006

Basándonos en la definición y ejemplos de la documentación Teradata, podemos hacer:

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT d.*
FROM TABLE (TD_SYSFNLIB.STRTOK_SPLIT_TO_TABLE(CARLOS.PRUEBA_08.ID_N, 
                                              CARLOS.PRUEBA_08.V_TXT, 
                                              ',')
            RETURNS (outkey integer, 
                     tokennum integer, 
                     token varchar(20) character set unicode) 
           ) as d 
ORDER BY 1,2;


 *** Query completed. 6 rows found. 3 columns returned.
 *** Total elapsed time was 1 second.

     outkey     tokennum  token
-----------  -----------  --------------------
          1            1  001
          2            1  002
          2            2  003
          4            1  004
          4            2  005
          4            3  006

¿Qué hemos tenido en cuenta? Pues que hemos elegido la columna ID_N como nuestra clave y la coma ‘,’ como nuestro separador. Fácil.

Pero supongamos que quermos ir un poco más allá: que queremos conservar en lo posible la estructura de la tabla original, con sus tres columnas (aunque podrían ser más). Entonces tenemos que recurrir a una versión un poco más complicada. Lo primero es que la clave elegida (inkey/outkey) puede ser VARCHAR y, aunque la documentación dice que puede ser de hasta 10 caracteres (VARCHAR(10)), lo cierto es que admite hasta VARCHAR(32). Otra cosa muy importante a tener en cuenta es que esta clave VARCHAR debe ser CHARACTER SET UNICODE (al igual que el valor de cada parte de la cadena devuelta).

Sabiendo esto, podemos concatenar las columnas que necesitemos (hasta un límite de VARCHAR(32)):

          
SELECT d.*
FROM TABLE (TD_SYSFNLIB.STRTOK_SPLIT_TO_TABLE(
               CAST(CARLOS.PRUEBA_08.ID_N AS CHAR(1)) || '|' ||
                  CARLOS.PRUEBA_08.C_TXT, 
               CARLOS.PRUEBA_08.V_TXT, 
               ',')
            RETURNS (
               outkey VARCHAR(32) character set unicode, 
               tokennum integer, 
               token varchar(20) character set unicode)
           ) d 
ORDER BY 1,2;


 *** Query completed. 6 rows found. 3 columns returned.
 *** Total elapsed time was 1 second.

outkey         tokennum  token
----------  -----------  --------------------
1|A                   1  001
2|B                   1  002
2|B                   2  003
4|D                   1  004
4|D                   2  005
4|D                   3  006

OK. Entonces podemos volver a descomponer la clave en sus columnas originales utilizando la función STRTOK(), que es prima hermana de nuestra STRTOK_SPLIT_TO_TABLE(), y utilizar simples CAST para que el resultado sea lo más parecido a nuestra tabla original:

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT CAST(TD_SYSFNLIB.STRTOK(d.OUTKEY,'|',1) AS INTEGER) ID_N,
       CAST(TD_SYSFNLIB.STRTOK(d.OUTKEY,'|',2) AS CHAR(1)) C_TXT,
       CAST(d.TOKEN AS VARCHAR(20)) V_TXT_PART
FROM TABLE (TD_SYSFNLIB.STRTOK_SPLIT_TO_TABLE(
               CAST(CARLOS.PRUEBA_08.ID_N AS CHAR(1)) || '|' ||
                  CARLOS.PRUEBA_08.C_TXT, 
               CARLOS.PRUEBA_08.V_TXT, 
               ',')
            RETURNS (OUTKEY VARCHAR(32) CHARACTER SET UNICODE, 
                     TOKENNUM INTEGER, 
                     TOKEN VARCHAR(20) CHARACTER SET UNICODE)
           ) d 
ORDER BY 1,2,3;


 *** Query completed. 6 rows found. 3 columns returned.
 *** Total elapsed time was 1 second.

       ID_N  C_TXT  V_TXT_PART
-----------  -----  -------------------------
          1  A      001
          2  B      002
          2  B      003
          4  D      004
          4  D      005
          4  D      006

Saludos.

Carlos.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: