Conversion de requêtes hiérarchiques Oracle vers InterSystems IRIS : Génération de plages de dates

Si vous migrez d'Oracle vers InterSystems IRIS, comme beaucoup de mes clients, vous risquez de rencontrer des modèles SQL spécifiques à Oracle nécessitant une conversion.

Prenons l'exemple suivant:

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

Dans Oracle:

• LEVEL est une pseudo-colonne utilisée dans les requêtes hiérarchiques (CONNECT BY). Elle commence à 1 et s'incrémente de 1.
• CONNECT BY LEVEL <= (...) détermine le nombre de lignes à générer.
• La différence entre les deux dates plus un donne 11, donc la requête génère 11 lignes, en comptant à rebours à partir du 12 mai 2023 jusqu'au 2 mai 2023.

Répartition du résultat:

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

La question est maintenant de savoir comment obtenir ce résultat dans InterSystems IRIS, qui ne prend pas en charge CONNECT BY?

Une solution consiste à implémenter une requête de type SQL à l'aide d'ObjectScript qui imite ce comportement. Vous trouverez ci-dessous un exemple de définition CREATE QUERY qui accepte une date de début STARTDATE et un nombre de jours DAYS, et renvoie la liste des dates par ordre descendant.

✅ InterSystems IRIS: mise en œuvre d'une requête de l'intervalle de date

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
}

Vous pouvez exécuter la commande CREATE QUERY Vous pouvez exécuter la commande CREATE QUERY ci-dessus dans le portail IRIS System Management, ou via un outil tel que DBeaver ou un éditeur Python/Jupyter Notebook utilisant JDBC/ODBC.

🧪 Exemple d'utilisation:

Pour générer le même résultat que la requête Oracle ci-dessus, utilisez:

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

Cela donnera le résultat suivant:

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

🔁 Utilisation avancée: Jointure avec d'autres tables

Vous pouvez également utiliser cette requête comme sous-requête ou dans des jointures:

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

Cela vous permet d'intégrer des plages de dates dans des flux SQL plus importants.

J'espère que cela sera utile à tous ceux qui sont confrontés à des scénarios de migration d'Oracle vers IRIS ! Si vous avez mis au point des solutions alternatives ou si vous proposez des améliorations, n'hésitez pas à me faire part de vos commentaires.