Vistas recursivas 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 convertir un SELECT recursivo en una vista, no basta con envolver dicha ‘SELECT’ dentro de un ‘CREATE VIEW’ y ya está:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
CREATE SET TABLE MY_DB.PRUEBA_REC,
   NO FALLBACK,
   NO JOURNAL,
   CHECKSUM=DEFAULT
   (  ID_N INTEGER NOT NULL,
      C_TXT VARCHAR(10) NOT NULL,
      ID_N_SUP INTEGER NULL
   )
   UNIQUE PRIMARY INDEX (ID_N)
;

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA_REC (ID_N, C_TXT, ID_N_SUP) VALUES (1,'UNO',10);

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA_REC (ID_N, C_TXT, ID_N_SUP) VALUES (2,'DOS',10);

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA_REC (ID_N, C_TXT, ID_N_SUP) VALUES (3,'TRES',10);

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA_REC (ID_N, C_TXT, ID_N_SUP) VALUES (10,'DIEZ',100)

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA_REC (ID_N, C_TXT, ID_N_SUP) VALUES (20,'VEINTE',10

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA_REC (ID_N, C_TXT, ID_N_SUP) VALUES (100,'CIEN',NUL

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT * FROM MY_DB.PRUEBA_REC ORDER BY ID_N DESC;

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

       ID_N  C_TXT          ID_N_SUP
-----------  ----------  -----------
        100  CIEN             (null)
         20  VEINTE              100
         10  DIEZ                100
          3  TRES                 10
          2  DOS                  10
          1  UNO                  10

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
WITH RECURSIVE RECURSIVA ( ID_N, C_TXT, ID_N_SUP, C_TXT_SUP )
AS
(
SELECT ID_N,
       C_TXT,
       ID_N_SUP,
       NULL (VARCHAR(10)) C_TXT_SUP
  FROM MY_DB.PRUEBA_REC
 WHERE ID_N_SUP IS NULL
UNION ALL
SELECT p.ID_N,
       p.C_TXT,
       p.ID_N_SUP,
       r.C_TXT
  FROM MY_DB.PRUEBA_REC p,
       RECURSIVA r
 WHERE p.ID_N_SUP = r.ID_N
)
SELECT *
  FROM RECURSIVA
  ORDER BY ID_N DESC
;

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

       ID_N  C_TXT          ID_N_SUP  C_TXT_SUP
-----------  ----------  -----------  ----------
        100  CIEN             (null)  (null)
         20  VEINTE              100  CIEN
         10  DIEZ                100  CIEN
          3  TRES                 10  DIEZ
          2  DOS                  10  DIEZ
          1  UNO                  10  DIEZ

En efecto: si intentamos simplemente envolver la ‘query’ en un CREATE VIEW’ tenemos que:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
CREATE VIEW MY_DB.PRUEBA_REC_VW
AS
WITH RECURSIVE RECURSIVA ( ID_N, C_TXT, ID_N_SUP, C_TXT_SUP )
AS
(
SELECT ID_N,
       C_TXT,
       ID_N_SUP,
       NULL (VARCHAR(10)) C_TXT_SUP
  FROM MY_DB.PRUEBA_REC
 WHERE ID_N_SUP IS NULL
UNION ALL
SELECT p.ID_N,
       p.C_TXT,
       p.ID_N_SUP,
       r.C_TXT
  FROM MY_DB.PRUEBA_REC p,
       RECURSIVA r
 WHERE p.ID_N_SUP = r.ID_N
)
SELECT *
  FROM RECURSIVA
;

 *** Failure 6926 WITH [RECURSIVE] clause or recursive view is not supported
 within WITH [RECURSIVE] definitions, views, triggers or stored procedures.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.

Esto es porque Teradata utiliza una sintaxis específica para la creación de vistas recursivas:


CREATE RECURSIVE VIEW rec_view (col1,col2…)
AS
( SELECT root.col1,
root.col2,

FROM THE_TABLE root
WHERE [condicion raíz]
UNION ALL
SELECT tt.col1,
tt.col2,

FROM rec_view rec,
THE_TABLE tt
WHERE [condicion enlace]
);

Así pues, convertimos nuestra ‘query’ recursiva en una vista recursiva:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
CREATE RECURSIVE VIEW MY_DB.PRUEBA_REC_VW ( ID_N, C_TXT, ID_N_SUP, C_TXT_SUP )
AS
(
SELECT ID_N,
       C_TXT,
       ID_N_SUP,
       NULL (VARCHAR(10)) C_TXT_SUP
  FROM MY_DB.PRUEBA_REC
 WHERE ID_N_SUP IS NULL
UNION ALL
SELECT p.ID_N,
       p.C_TXT,
       p.ID_N_SUP,
       r.C_TXT
  FROM MY_DB.PRUEBA_REC p,
       MY_DB.PRUEBA_REC_VW r
 WHERE p.ID_N_SUP = r.ID_N
)
;

 *** Failure 3807 Object 'MY_DB.PRUEBA_REC_VW' does not exist.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.

Pero ¿qué pasa?. ¡Está devolviendo un error! ¿Por qué? La sintaxis es correcta.

Además está diciendo que el objeto (la vista recursiva) no existe. ¡Claro que no existe, lo estamos creando! ¡El problema sería el contrario: que existiera antes del CREATE VIEW! Esto parece el típico error de ¿Quién fue primero, la gallina o el huevo? Estamos creando una vista que hace referencia a sí misma (de ahí lo de ‘RECURSIVE’), pero no se puede resolver esa referencia porque dicho objeto aún no está creado (¡¡!!). Esto huele a ‘catch-22‘.

La documentación de Teradata tampoco es de gran ayuda aquí: ni la expliación de la sintaxis ni los ejemplos arrojan luz alguna de por qué falla la creación de la vista anterior.

Si comenzais -como yo- a hacer pruebas ensayo/error conseguireis dar con la solución. Pero si quereis ahorraros ese tiempo, ya os la digo yo: la respuesta está en que la referencia a la vista recursiva ‘raíz’ en la parte de la ‘join’ inferior (parte ‘recursiva’ tras el ‘UNION ALL’) no puede contener el nombre de la base de datos de la vista, ya que entonces irá a comprobar el diccionario de datos y se producirá el error.

Así que cambiamos ‘MY_DB.PRUEBA_REC_VW r’ por ‘PRUEBA_REC_VW r’ y todo funciona:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
REPLACE RECURSIVE VIEW MY_DB.PRUEBA_REC_VW ( ID_N, C_TXT, ID_N_SUP, C_TXT_SUP )
AS
(
SELECT ID_N,
       C_TXT,
       ID_N_SUP,
       NULL (VARCHAR(10)) C_TXT_SUP
  FROM MY_DB.PRUEBA_REC
 WHERE ID_N_SUP IS NULL
UNION ALL
SELECT p.ID_N,
       p.C_TXT,
       p.ID_N_SUP,
       r.C_TXT
  FROM MY_DB.PRUEBA_REC p,
       PRUEBA_REC_VW r   --Sin el nombre de la base de datos!!
 WHERE p.ID_N_SUP = r.ID_N
)
;

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT * FROM MY_DB.PRUEBA_REC_VW
ORDER BY ID_N DESC;

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

       ID_N  C_TXT          ID_N_SUP  C_TXT_SUP
-----------  ----------  -----------  ----------
        100  CIEN             (null)  (null)
         20  VEINTE              100  CIEN
         10  DIEZ                100  CIEN
          3  TRES                 10  DIEZ
          2  DOS                  10  DIEZ
          1  UNO                  10  DIEZ

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: