‘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.

Anuncios

8 respuestas a ‘Queries’ Recursivas en Teradata.

  1. PAul dice:

    Que tal Carlos !!!
    Una consulta, existe la posibilidad de ejecutar las estadísitcas mediante un cursor ???
    Te pregunto porque intenté hacerlo pero no me ha salido 😦

    Y una consulta más:
    Vos sacas estadísiticas para que campos ??? Solo INIDICES (UPI, NUPI, USI y NUSI)
    o a otros campos ??? Ejemplo: Fechas, localidades, etc

    Bueno, espero que puedas responder a mis consultas

    Saludos,

    • carlosal dice:

      Paul:

      ¿Con un cursor? No veo cómo (a no ser que te refieras a un ‘stored procedure’ que incluya SQL dinámico para ejecutar los ‘COLLECT’).

      En cuanto a qué columnas/índices son susceptibles de calcular las estadísticas: pues -como siempre- depente de infinidad de cosas. Los PI, seguro, y luego lo demás en función del diseño, accesos, necesidades, espacio, etc…

      Saludos.

      Carlos.

  2. PAul dice:

    Hola Carlos !
    Si me refería mediante un SP con un cursor dentro de éste

  3. Kristian dice:

    Carlos, tengo un problema que al parecer puedo solucionar con querys recursivas, pero no estoy seguro como…

    Tengo 4 columnas, A, B, C, D, de las cuales las ultimas dependen entre ellas, es decir,

    C, depende de B y A
    D, depende de C
    B, depende del D anterior (fila anterior)

    Al decir que X depende de Y, me refiero a que tengo un “case when Y=1 then 2 as X”

    Ojala me puedas ayudar!

    Saludos!

  4. […] en Teradata: el huevo o la gallina. Ya hemos visto en varias ocasiones -por ejemplo aquí- que Teradata soporta la recursividad en SELECT’s. Pero, a diferencia de Oracle, si queremos […]

  5. Mithafashi dice:

    Bravo!!!
    This is working

  6. […] viejo problema de convertir filas a cadenas de caracteres es un tema recurrente y ha sido tratado aquí. Normalmente hay que recurrir a algún tipo de recursividad (incluso en Oracle) y suele resultar un […]

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: