adaptation d'une requete oracle (start with) a sql server (CTE)

Version imprimable

Bonjour a tous,

Je viens vers vous car j'éprouve quelques difficultés à adapter ma requete Oracle sur SqlServer.

La requête Oracle utilise des éléments spécifique à oracle comme :
- Start with
- connect by ... prior
- sys_connect_by_path
Et l'implémentation des CTE m'étant inconnue, je galère

Voici ma requete oracle.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 sr1 AS ( SELECT table1, table2, sql FROM matable UNION SELECT table2, table1, sql FROM matable ), sr2 AS ( SELECT level, table1, table2, ltrim(sys_connect_by_path(sql, ' AND '), ' AND ') AS chemin FROM sr1 WHERE table2 = '"+tab2+"' START WITH table1 = '"+tab1+"' CONNECT BY NOCYCLE table1 = PRIOR table2 ORDER BY level ASC ) SELECT chemin FROM sr2 WHERE rownum = 1
Si quelqu'un a une idée ca serait super

merci d'avance

Ghosty

14/03/2011, 17h57
SQLpro

START WITH / CONNECT BY sont des atrocités spécifique à Oracle.
La norme SQL c'est WITH... avec des CTE.
Lisez mon article à ce sujet, sur les requêtes récursives :
http://sqlpro.developpez.com/cours/s...te-recursives/

A +
14/03/2011, 21h26
Waldar

Citation:

Envoyé par SQLpro

START WITH / CONNECT BY sont des atrocités spécifique à Oracle

Des atrocités qui ont résolu les problèmes hiérarchiques entre 1977 et l'arrivée de la norme en 1999 sur ce sujet.
Vous pouvez à juste titre critiquer les dix ans qu'il aura fallu à l'éditeur pour implémenter les rCTE !

Je connais votre article et je m'en sert déjà comme base de travail.

J'arrive déjà à avoir de la récursivité mais celle ci ne me permet pas de trouver le chemin désiré.
(Pour plus d'information sur mon problème ce référé au sujet suivant ou waldar ma déjà aidé : http://www.developpez.net/forums/d10...cte-recursion/)

voila ma requête pour le moment.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 sr1(table1, table2, steps, chemin) AS ( (SELECT table1, table2, 0, CAST('GTETS >>' AS VARCHAR(MAX)) FROM matable WHERE table1 = 'GTETS' UNION SELECT table1, table2, 0, CAST('GTETS >>' AS VARCHAR(MAX)) FROM matable WHERE table1 = 'GTETS') UNION ALL SELECT depart.table1, depart.table2, fin.steps + 1, fin.chemin + ' -> ' + depart.table1 + '-' + depart.table2 FROM sr1 AS fin INNER JOIN matable AS depart ON depart.table1 = fin.table2 ) SELECT * FROM sr1 ORDER BY steps DESC
Normalement cette requête devrait me retourner tous les chemins au départ de GTETS (je rajouterais ensuite l'arrivée dans le dernier select) mais la requête ne me retourne pas tous les chemins

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
sr1(table1, table2, steps, chemin) 
AS ( 
	(SELECT table1, table2, 0, CAST('GTETS >>' AS VARCHAR(MAX)) 
	FROM matable 
	WHERE table1 = 'GTETS' 
	UNION ALL
	SELECT depart.table1, depart.table2, fin.steps + 1, fin.chemin + ' -> ' + depart.table1 + '-' + depart.table2
	FROM sr1 AS fin
		INNER JOIN matable AS depart
			ON depart.table1 = fin.table2 
)
SELECT * 
FROM sr1
ORDER BY steps DESC

Me parait plus clair....

A +

15/03/2011, 10h01
ghosty177

Je vois que tu a supprimer le premier "union" seulement celui me permettait d'avoir tous mes chemins.

Je m'explique, ma table de type (table1,table2,sql) contient mes relations entre les différentes tables. seulement table1 et table2 ne sont pas normalisées, du coup la table1 peut être table père puis table fille dans une autre relation.

sinon au niveau des résultats les deux requêtes retournes les mêmes résultats, mais ma requête ne retourne toujours pas les bons chemins.
15/03/2011, 11h02
Waldar

N'hesitez pas à publier un petit jeu de test.

ah oui désolé je pensait que le lien vers l'autre forum suffisait.

Du coup je passe par une table temporaire donc ma requête se présente ainsi :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
WITH matable (table1,table2,sql) AS ( 
 SELECT 'SVACS' AS table1, 'SVLCV' AS table2, 'svacs.etssvacs = svlcv.etssvlcv and svacs.artsvacs = svlcv.artsvlcv and svacs.actsvacs = ''S''' AS sql UNION ALL 
 SELECT 'ACS_A' AS table1, 'STCFA' AS table2, 'ACS_A.etssvacs = stcfa.etsstcfa and ACS_A.farsvacs = stcfa.fapstcfa and ACS_A.actsvacs = ''A''' AS sql UNION ALL 
 SELECT 'ACS_M' AS table1, 'SVLCV' AS table2, 'ACS_M.etssvacs = svlcv.etssvlcv and ACS_M.farsvacs = svlcv.artsvlcv and ACS_M.actsvacs = ''M''' AS sql UNION ALL 
 SELECT 'SVCVG' AS table1, 'SVCDV' AS table2, 'svcvg.nuisvcvg (+)= svcdv.nuisvcdv and svcvg.fonsvcvg (+)= ''VRP'' and svcvg.rolsvcvg (+)= ''V''' AS sql UNION ALL 
 SELECT 'SVELC' AS table1, 'SVCDV' AS table2, 'svelc.nuisvelc = svcdv.nuisvcdv' AS sql UNION ALL 
 SELECT 'SVEXP' AS table1, 'SVCDV' AS table2, 'svexp.numsvexp (+)= svcdv.expsvcdv and svexp.etssvexp (+)= svcdv.etssvcdv' AS sql UNION ALL 
 SELECT 'SVFAV' AS table1, 'SVCDV' AS table2, 'svfav.numsvfav (+)= svcdv.favsvcdv and svfav.etssvfav (+)= svcdv.etssvcdv' AS sql UNION ALL 
 SELECT 'SVLCV' AS table1, 'SVCDV' AS table2, 'svlcv.nuisvlcv = svcdv.nuisvcdv' AS sql UNION ALL 
 SELECT 'SVCDV' AS table1, 'SVNCV' AS table2, 'svcdv.clasvcdv = svncv.clasvncv and svcdv.etssvcdv = svncv.etssvncv' AS sql UNION ALL 
 SELECT 'SGECD' AS table1, 'SVCLI' AS table2, 'sgecd.echsgecd = svcli.echsvcli' AS sql UNION ALL 
 SELECT 'OEGES' AS table1, 'SVCVG' AS table2, 'oeges.numoeges (+)= svcvg.gessvcvg' AS sql UNION ALL 
 SELECT 'DEP_EXP' AS table1, 'SVEXP' AS table2, 'Dep_Exp.etssgdep = svexp.etssvexp and Dep_Exp.numsgdep = svexp.depsvexp' AS sql UNION ALL 
 SELECT 'ADR_FAC' AS table1, 'SVFAV' AS table2, 'Adr_Fac.numoetia = svfav.tiasvfav and Adr_Fac.tieoetia = svfav.tiesvfav' AS sql UNION ALL 
 SELECT 'ADR_PAI_FAC' AS table1, 'SVFAV' AS table2, 'Adr_Pai_Fac.numoetia (+)= svfav.tapsvfav and Adr_Pai_Fac.tieoetia (+)= svfav.tipsvfav' AS sql UNION ALL 
 SELECT 'STCFA' AS table1, 'SVLCV' AS table2, 'stcfa.etsstcfa = svlcv.etssvlcv and stcfa.fafstcfa = svlcv.artsvlcv and stcfa.fapstcfa = ''ICTYANE'' and stcfa.rftstcfa = ''AL''' AS sql UNION ALL 
 SELECT 'SGART' AS table1, 'SVLCV' AS table2, 'sgart.numsgart = svlcv.artsvlcv' AS sql UNION ALL 
 SELECT 'SVCDV' AS table1, 'DEV_CDE' AS table2, 'svcdv.devsvcdv = Dev_Cde.numoedev' AS sql UNION ALL 
 SELECT 'SGART' AS table1, 'SGAAE' AS table2, 'sgart.numsgart = sgaae.artsgaae' AS sql UNION ALL 
 SELECT 'ATV_ARU_GAMME' AS table1, 'SGATV' AS table2, 'Atv_Aru_GAMME.numsgaru (+)= sgatv.artsgatv and Atv_Aru_GAMME.etssgaru (+)= sgatv.etssgatv and Atv_Aru_GAMME.entsgaru (+)= ''SGATV'' and Atv_Aru_GAMME.rubsgaru (+)= ''GAMME''' AS sql UNION ALL 
 SELECT 'SKSTI' AS table1, 'SGATV' AS table2, 'sksti.artsksti (+)= sgatv.artsgatv' AS sql UNION ALL 
 SELECT 'GTETS' AS table1, 'REF' AS table2, 'gtets.devgtets = Ref.numoedev' AS sql UNION ALL 
 SELECT 'REP' AS table1, 'GTETS' AS table2, 'Rep.numoedev (+)= gtets.dvrgtets' AS sql UNION ALL 
 SELECT 'GTETS' AS table1, 'SVCDV' AS table2, 'gtets.numgtets = svcdv.etssvcdv' AS sql UNION ALL 
 SELECT 'GES_AETS' AS table1, 'SGAAE' AS table2, 'Ges_AEts.numoeges (+)= sgaae.gessgaae' AS sql   UNION ALL 
 SELECT 'CAT_AVTE_P' AS table1, 'SGATV' AS table2, 'CAT_AVte_P.occgtpar (+)= sgatv.catsgatv and CAT_AVte_P.numgtets (+)= sgatv.etssgatv and CAT_AVte_P.padgtpar (+)= ''CATSGATV''' AS sql UNION ALL 
 SELECT 'FNV_AETS_P' AS table1, 'SGAAE' AS table2, 'FNV_AEts_P.occgtpar (+)= sgaae.fnvsgaae and FNV_AEts_P.numgtets (+)= sgaae.etssgaae and FNV_AEts_P.padgtpar (+)= ''FNVSGAAE''' AS sql UNION ALL 
 SELECT 'FON_AETS_P' AS table1, 'SGAAE' AS table2, 'FON_AEts_P.occgtpar (+)= sgaae.fonsgaae and FON_AEts_P.numgtets (+)= sgaae.etssgaae and FON_AEts_P.padgtpar (+)= ''FONSGART''' AS sql UNION ALL 
 SELECT 'SGAAE' AS table1, 'SGATV' AS table2, 'sgaae.etssgaae = sgatv.etssgatv and sgaae.artsgaae = sgatv.artsgatv' AS sql UNION ALL 
 SELECT 'ATV_ARU_CHEF_PRD' AS table1, 'SGATV' AS table2, 'Atv_Aru_CHEF_PRD.numsgaru (+)= sgatv.artsgatv and Atv_Aru_CHEF_PRD.etssgaru (+)= sgatv.etssgatv and Atv_Aru_CHEF_PRD.entsgaru (+)= ''SGATV'' and Atv_Aru_CHEF_PRD.rubsgaru (+)= ''CHEF-PRD''' AS sql UNION ALL 
 SELECT 'ATV_ARU_TYPE_ART' AS table1,  'SGATV' AS table2, 'Atv_Aru_TYPE_ART.numsgaru (+)= sgatv.artsgatv and Atv_Aru_TYPE_ART.etssgaru (+)= sgatv.etssgatv and Atv_Aru_TYPE_ART.entsgaru (+)= ''SGATV'' and Atv_Aru_TYPE_ART.rubsgaru (+)= ''TYPE-ART''' AS sql UNION ALL 
 SELECT 'GES_AVTE' AS table1, 'SGATV' AS table2, 'Ges_AVte.numoeges (+)= sgatv.gessgatv' AS sql   UNION ALL 
 SELECT 'TVA_AVTE' AS table1, 'SGATV' AS table2, 'TVA_AVte.numoetva (+)= sgatv.tvvsgatv and TVA_AVte.etsoetva (+)= sgatv.etssgatv' AS sql UNION ALL 
 SELECT 'DEP_AVTE' AS table1, 'SGATV' AS table2, 'Dep_AVte.numsgdep (+)= sgatv.depsgatv and Dep_AVte.etssgdep (+)= sgatv.etssgatv' AS sql UNION ALL 
 SELECT 'SKDRE' AS table1, 'SGATV' AS table2, 'skdre.etsskdre (+)= sgatv.etssgatv and skdre.artskdre (+)= sgatv.artsgatv' AS sql UNION ALL 
 SELECT 'MODE_AETS' AS table1, 'SGAAE' AS table2, 'Mode_AEts.numsgmdv (+)= sgaae.movsgaae and Mode_AEts.etssgmdv (+)= sgaae.etssgaae' AS sql UNION ALL 
 SELECT 'UNI_FAC' AS table1, 'SGART' AS table2, 'Uni_Fac.numsguni (+)= sgart.unfsgart' AS sql UNION ALL 
 SELECT 'UNI_LIV' AS table1, 'SGART' AS table2, 'Uni_Liv.numsguni (+)= sgart.unlsgart' AS sql UNION ALL 
 SELECT 'UNI_VTE' AS table1, 'SGART' AS table2, 'Uni_Vte.numsguni (+)= sgart.unvsgart' AS sql UNION ALL 
 SELECT 'SKLMS' AS table1, 'SGATV' AS table2, 'sklms.etosklms (+)= sgatv.etssgatv and sklms.artsklms (+)= sgatv.artsgatv' AS sql  UNION ALL 
 SELECT 'SKLMS' AS table1, 'SKMVS' AS table2, 'sklms.nuisklms = skmvs.nuiskmvs' AS sql UNION ALL 
 SELECT 'ADR_CDE' AS table1, 'SVCDV' AS table2, 'Adr_Cde.numoetia (+)= svcdv.tacsvcdv and Adr_Cde.tieoetia (+)= svcdv.ticsvcdv' AS sql UNION ALL 
 SELECT 'ADR_LIV_CDE' AS table1, 'SVCDV' AS table2, 'Adr_Liv_Cde.numoetia = svcdv.talsvcdv and Adr_Liv_Cde.tieoetia = svcdv.tilsvcdv' AS sql UNION ALL 
 SELECT 'DEP_CDE' AS table1, 'SVCDV' AS table2, 'Dep_Cde.etssgdep = svcdv.etssvcdv and Dep_Cde.numsgdep = svcdv.depsvcdv' AS sql   UNION ALL 
 SELECT 'ETP_CDE' AS table1, 'SVCDV' AS table2, 'Etp_Cde.etssgetc = svcdv.etssvcdv and Etp_Cde.etpsgetc = svcdv.ecvsvcdv and Etp_Cde.clasgetc = svcdv.clasvcdv and Etp_Cde.domsgetc = ''V''' AS sql UNION ALL 
 SELECT 'CDV' AS table1, 'SKDRE' AS table2, 'CDV.nuisvcdv = skdre.nuiskdre' AS sql UNION ALL 
 SELECT 'SVCDV' AS table1, 'SKDRE' AS table2, 'svcdv.ecvsvcdv <= 158' AS sql UNION ALL 
 SELECT 'SVCDV' AS table1, 'SVCLI' AS table2, 'svcdv.clisvcdv (+)= svcli.numsvcli and svcdv.etssvcdv (+)= svcli.etssvcli' AS sql UNION ALL 
 SELECT 'SVCVE' AS table1, 'SVCDV' AS table2, 'svcve.nuisvcve (+)= svcdv.nuisvcdv' AS sql UNION ALL 
 SELECT 'CVF_3000' AS table1, 'SVCDV' AS table2, 'cvf_3000.nuisvcvf (+)= svcdv.nuisvcdv and cvf_3000.cfgsvcvf (+)= 3000' AS sql  																 
), 
sr1(table1, table2, steps, chemin) AS ( 
	(SELECT table1, table2, 0, CAST('GTETS >>' AS VARCHAR(MAX)) FROM matable WHERE table1 = 'GTETS' 
	UNION 
	SELECT table1, table2, 0, CAST('GTETS >>' AS VARCHAR(MAX)) FROM matable WHERE table1 = 'GTETS') 
	UNION ALL
	SELECT depart.table1, depart.table2, fin.steps + 1, fin.chemin + ' -> ' + depart.table1 + '-' + depart.table2
	FROM sr1 AS fin
		INNER JOIN matable AS depart ON depart.table1 = fin.table2 
)
SELECT * 
FROM sr1
ORDER BY steps DESC

Le but étant comme sur l'autre discussion de trouver le chemin par un requête récursive entre deux tables.

comme les exemple ci dessous :

le chemin entre SVFAV et SGART A savoir :

Code:

SVCDV-SVFAV > SVCDV-SVLCV > SGART-SVLCV

Autre exemple avec SVEXP et GTEST :

Code:

SVCDV-SVEXP ; GTETS-SVCDV

et un exemple simple entre SVCDV et SVETC :

Code:

SVCDV-SGETC

Alors essayez :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
WITH 
T (table1, table2, steps, chemin) AS
(SELECT table1, table2, 0, CAST('GTETS >>' AS VARCHAR(MAX)) 
 FROM   matable 
 WHERE  table1 = 'GTETS'
 UNION
 SELECT table2, table1, 0, CAST('GTETS >>' AS VARCHAR(MAX)) 
 FROM   matable 
 WHERE  table2 = 'GTETS'),
sr1 (table1, table2, steps, chemin) AS
(SELECT table1, table2, steps, chemin 
 FROM T 
 UNION ALL
 SELECT depart.table1, depart.table2, fin.steps + 1, fin.chemin + ' -> ' + depart.table1 + '-' +  depart.table2
	FROM sr1 AS fin
		INNER JOIN T AS depart
			ON depart.table1 = fin.table2 
)
SELECT * 
FROM   sr1
ORDER  BY steps DESC

A +

15/03/2011, 15h02
ghosty177

On progresse, on progresse.

Si je met ta requête telle quelle, la requête me retourne toutes les jointures que la table a.

Seulement si je rajoute une table de destination dans le dernier Select, la requête ne me retourne que chemin pour le dernier exemple. A savoir que la table de destination et en relation directe à celle de départ.

Dans le cas où il y a une ou plusieurs relations entre ces deux tables, la requête ne retourne rien.

La requête actuelle ne devrait-elle pas retourner tous les chemins possible au départ de la table passé en paramètre plutôt que les tables a relations directes?
15/03/2011, 15h20
Waldar

Citation:

Envoyé par ghosty177

ah oui désolé je pensait que le lien vers l'autre forum suffisait.

Au temps pour moi, je lisais le sujet sur mon smartphone et j'ai zappé le lien.
Cela dit, c'est toujours bien d'adapter le jeu de test au SGBD qui doit répondre à la problématique !

Vous ferez attention pour la suite de votre traitement, car les jointures avec (+) sont spécifiques à Oracle.
15/03/2011, 15h54
ghosty177

Pas de problème en ce qui concerne le lien.

Sinon merci pour les relations et c'est vrai que je n'ai pas fait attention a cela car je teste sur un outil de la base pour le moment le temps de mettre en forme la requête.

Et du coup la partie avec les (+)= est générée automatiquement dans mon application en fonction de la base de données (et la j'ai repris ce que m'avais généré pour oracle).