Convertir consultas jerárquicas de Oracle a InterSystems IRIS: generar rangos de fechas

Si estáis migrando de Oracle a InterSystems IRIS, como muchos de mis clientes, podríais encontraros con patrones SQL específicos de Oracle que necesitan ser traducidos.

Tomad este ejemplo:

SELECT (TO_DATE('2023-05-12','YYYY-MM-DD') - LEVEL + 1) AS gap_date
FROM dual
CONNECT BY LEVEL <= (TO_DATE('2023-05-12','YYYY-MM-DD') - TO_DATE('2023-05-02','YYYY-MM-DD') + 1);

En Oracle:

• LEVEL es una pseudocolumna usada en consultas jerárquicas (CONNECT BY). Comienza en 1 y se incrementa de 1 en 1.
• CONNECT BY LEVEL <= (...) determina cuántas filas se van a generar.
• La diferencia entre las dos fechas más uno da 11, así que la consulta produce 11 filas, contando hacia atrás desde el 12 de mayo de 2023 hasta el 2 de mayo de 2023.

Desglose del resultado:

LEVEL = 1  → 2023-05-12
LEVEL = 2  → 2023-05-11
...
LEVEL = 11 → 2023-05-02

Ahora la pregunta es: ¿Cómo conseguís esto en InterSystems IRIS, que no admite CONNECT BY?

Una solución es implementar una consulta al estilo SQL usando ObjectScript que imite este comportamiento. A continuación se muestra un ejemplo de definición CREATE QUERY que acepta una STARTDATE y un número de DAYS, y devuelve la lista descendente de fechas.

✅ InterSystems IRIS: Implementación de una consulta de intervalo de fechas

CREATE QUERY GET_GAP_DATE(IN STARTDATE DATE, IN DAYS INT)
  RESULTS (GAP_DATE DATE)
  PROCEDURE
  LANGUAGE OBJECTSCRIPT

  Execute(INOUT QHandle BINARY(255), IN STARTDATE DATE, IN DAYS INT)
  {
    SET QHandle("start") = STARTDATE
    SET QHandle("days")  = DAYS
    SET QHandle("level") = 1
    RETURN $$$OK
  }

  Fetch(INOUT QHandle BINARY(255), INOUT Row %List, INOUT AtEnd INT)
  {
    IF (QHandle("level") > QHandle("days")) {
      SET Row = ""
      SET AtEnd = 1
    } ELSE {
      SET Row = $ListBuild(QHandle("start") - QHandle("level") + 1)
      SET QHandle("level") = QHandle("level") + 1
    }
    RETURN $$$OK
  }

  Close(INOUT QHandle BINARY(255))
  {
    KILL QHandle
    QUIT $$$OK
  }

Podéis ejecutar la instrucción CREATE QUERY anterior en el Portal de Gestión del Sistema de IRIS, o mediante una herramienta como DBeaver o un notebook de Python/Jupyter usando JDBC/ODBC.

🧪 Ejemplo de uso:

Para generar el mismo resultado que la consulta de Oracle anterior, usad:

SELECT * FROM GET_GAP_DATE(
  TO_DATE('2023-05-12', 'YYYY-MM-DD'),
  TO_DATE('2023-05-12', 'YYYY-MM-DD') - TO_DATE('2023-05-02', 'YYYY-MM-DD') + 1
);

Esto generará:

GAP_DATE
----------
2023-05-12
2023-05-11
...
2023-05-02
(11 rows)

🔁 Uso avanzado: Unir con otras tablas

También podéis usar esta consulta como subconsulta o en joins:

SELECT * 
FROM GET_GAP_DATE(TO_DATE('2023-05-12', 'YYYY-MM-DD'), 11) 
CROSS JOIN dual;

Esto os permite integrar rangos de fechas en flujos de trabajo SQL más amplios.

¡Espero que esto ayude a quien esté enfrentando escenarios de migración de Oracle a IRIS! Si habéis creado soluciones alternativas o tenéis mejoras, me encantaría conocer vuestras opiniones.