‘Queries’ Recursivas en Teradata.

El SQL recursivo es algo que muy pocos utilizan -sobre todo por mero y simple desconocimiento- pero que en determinadas ocasiones resulta ser una herramienta muy útil. Ya hemos visto ejemplos de ello en Oracle: calcular un factorial, convertir cadenas a tablas y tablas a cadenas, pero hay muchas otras tareas para las que el SQL recursivo puede ser el camino a seguir.

Como vimos, Oracle implementa la recursividad mediante la cláusula ‘CONNECT BY’, pero ¿existe algo así en Teradata? La respuesta es sí.

En Teradata la recursividad se implementa con una sintaxis que tiene por estructura algo así:

WITH RECURSIVE nombre_tabla (col1, col2, col3...)
AS (
   SELECT "RAIZ"
   UNION ALL
   SELECT "RECURSIVO" (que hace referencia al "RAIZ" por medio de nombre_tabla)
   )
SELECT "TOTAL"        (sobre nombre_tabla)

El truco del asunto está en comprender que nombre_tabla es una tabla temporal. Dicha tabla se va llenando con los resultados del SELECT “RAIZ” PRIMERO, y luego con los del SELECT “RECURSIVO” en cada ejecución. Así, cada ejecución del SELECT “RECURSIVO” se realiza contra contra esta tabla temporal que tiene los resultados de la ejecución anterior. Esto se repite hasta que el SELECT “RECURSIVO” deja de retornar filas. Entonces se ejecuta el SELECT “TOTAL” contra la tabla temporal nombre_tabla.

Visto así es mucho más sencillo de lo que parecía al principio (¿o no?).

Para ver un ejemplo de cómo funciona, vamos a hacer algo parecido a lo que hicimos en Oracle para convertir filas a cadenas de caracteres, pero lo vamos a utilizar aquí para mostrar los índices de las tablas de una base de datos con las columnas que los componen en su orden de definición:

 Teradata BTEQ 08.02.03.03 for WIN32.
 Copyright 1984-2006, NCR Corporation. ALL RIGHTS RESERVED.
 Enter your logon or BTEQ command:
.LOGON DBNAME/yyyyyy

.LOGON DDBNAME/yyyyyy
Password:

 *** Logon successfully completed.
 *** Teradata Database Release is V2R.06.02.01.18
 *** Teradata Database Version is 06.02.01.17
 *** Transaction Semantics are BTET.
 *** Character Set Name is 'ASCII'.

 *** Total elapsed time was 3 seconds.

WITH RECURSIVE JERARQUICA (DATABASENAME,
                           TABLENAME,
                           INDEXNAME,
                           COLUMNNAME,
                           COLUMNPOSITION)
AS
( SELECT a.DATABASENAME,
         a.TABLENAME,
         COALESCE(a.INDEXNAME,'PI') INDEXNAME,
         CAST(TRIM(a.COLUMNNAME) AS VARCHAR(128)),
         a.COLUMNPOSITION
    FROM DBC.INDICES a
   WHERE a.DATABASENAME='DBNAME'
     AND a.COLUMNPOSITION = 1
UNION ALL
  SELECT a.DATABASENAME,
         a.TABLENAME,
         COALESCE(a.INDEXNAME,'PI') INDEXNAME,
         b.COLUMNNAME || ',' || TRIM(a.COLUMNNAME) COLUMNNAME,
         a.COLUMNPOSITION
    FROM DBC.INDICES a,
         JERARQUICA b
   WHERE a.DATABASENAME   = b.DATABASENAME
     AND a.TABLENAME      = b.TABLENAME
     AND COALESCE(a.INDEXNAME,'PI') = b.INDEXNAME
     AND a.COLUMNPOSITION = b.COLUMNPOSITION + 1
)
SELECT DATABASENAME,
       TABLENAME,
       INDEXNAME,
       MAX(COLUMNNAME) COLUMNAS
  FROM JERARQUICA
GROUP BY DATABASENAME, TABLENAME, INDEXNAME
;

DATABASENAME  TABLENAME  INDEXNAME  COLUMNAS
------------  ---------  ---------  -----------------
DBNAME        TABLE01    PI         COL01
DBNAME        TABLE02    PI         COL01
DBNAME        TABLE03    PI         COL01,COL02,COL03
DBNAME        TABLE04    PI         COL01
DBNAME        TABLE05    PI         COL01
DBNAME        TABLE06    PI         COL01,COL02
DBNAME        TABLE07    PI         COL01,COL02,COL03
DBNAME        TABLE08    PI         COL01,COL02
DBNAME        TABLE09    PI         COL01,COL02,COL03
...

Evidentemente los nombres de la base de datos, las tablas y las columnas se han ocultado, pero probad en vuestro entorno y veréis lo bien que funciona…

Saludos.

Carlos.