Converting Oracle Hierarchical Queries to InterSystems IRIS: Generating Date Ranges

If you're migrating from Oracle to InterSystems IRIS—like many of my customers—you may run into Oracle-specific SQL patterns that need translation.

Take this example:

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);

In Oracle:

• LEVEL is a pseudo-column used in hierarchical queries (CONNECT BY). It starts at 1 and increments by 1.
• CONNECT BY LEVEL <= (...) determines how many rows to generate.
• The difference between the two dates plus one gives 11, so the query produces 11 rows, counting backwards from May 12, 2023 to May 2, 2023.

Breakdown of the result:

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

Now the question is: How do you achieve this in InterSystems IRIS, which doesn’t support CONNECT BY?

One solution is to implement a SQL-style query using ObjectScript that mimics this behavior. Below is a sample CREATE QUERY definition that accepts a STARTDATE and a number of DAYS, and returns the descending list of dates.

✅ InterSystems IRIS: Implementing a Date Gap Query

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
  }

You can run the above CREATE QUERY in IRIS System Management Portal, or through a tool like DBeaver or a Python/Jupyter notebook using JDBC/ODBC.

🧪 Example Usage:

To generate the same result as the Oracle query above, use:

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
);

This will output:

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

🔁 Advanced Usage: Join with Other Tables

You can also use this query as a subquery or in joins:

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

This allows you to integrate date ranges into larger SQL workflows.

Hope this helps anyone tackling Oracle-to-IRIS migration scenarios! If you’ve built alternative solutions or have improvements, I’d love to hear your thoughts.