Bonjour à tous,

Je viens vers vous car je suis entrain de travaillé sur la récursivité sur une base SQL Server.
Et je dois adapter des requêtes oracle pour cette base de données. Pour la grande majorité j'y suis arrivé sans souci seulement je me retrouve face à une requête oracle utilisant la récursivité (Start with ... connect by...).

Voici la requête en question qui fonctionne bien sur Oracle.

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
WITH matable AS ( 
SELECT 'GTETS' AS table1, 'SVCDV' AS table2, 'gtets.numgtets = svcdv.etssvcdv' AS sql 										FROM dual UNION ALL 
SELECT 'SGART' AS table1, 'SVLCV' AS table2, 'sgart.numsgart = svlcv.artsvlcv' AS sql 										FROM dual UNION ALL 
SELECT 'SVCDV' AS table1, 'SKDRE' AS table2, 'svcdv.ecvsvcdv <= 158' AS sql 												FROM dual UNION ALL 
SELECT 'SVFAV' AS table1, 'SVCDV' AS table2, 'svfav.numsvfav = svcdv.favsvcdv and svfav.etssvfav = svcdv.etssvcdv' AS sql 	FROM dual UNION ALL 
SELECT 'SVCDV' AS table1, 'SVCLI' AS table2, 'svcdv.clisvcdv = svcli.numsvcli and svcdv.etssvcdv = svcli.etssvcli' AS sql 	FROM dual UNION ALL 
SELECT 'SVCVE' AS table1, 'SVCDV' AS table2, 'svcve.nuisvcve = svcdv.nuisvcdv' AS sql 										FROM dual UNION ALL 
SELECT 'SVLCV' AS table1, 'SVCDV' AS table2, 'svlcv.nuisvlcv = svcdv.nuisvcdv' AS sql 										FROM dual UNION ALL 
SELECT 'SGART' AS table1, 'SGAAE' AS table2, 'sgart.numsgart = sgaae.artsgaae' AS sql 										FROM dual 
), 
sr1 AS ( 
SELECT table1, table2, sql FROM matable UNION SELECT table2, table1, sql FROM matable 
), 
sr2 AS ( 
SELECT level, table1, table2, sys_connect_by_path(sql, ' AND ') AS chemin FROM sr1 WHERE table2 = 'SVFAV'  
START WITH table1 = 'SGART' CONNECT BY NOCYCLE table1 = PRIOR table2 ORDER BY level ASC 
) 
SELECT chemin FROM sr2 WHERE rownum = 1

N'arrivant pas à l'adapter malgré mes recherche et le cours se sqlpro sur le forum, je me tourne vers vous.

Merci d'avance

Ghosty