[SQLSRV2K5] - Requete pour hierarchie

Bonjour,

je suis en train d'essayer de construire une requête sql pour créer une hierarchie (5 niveaux)
les données sont dans une table avec "Code Parent" , "Code enfant" et un niveau.

J'ai fait la requête suivante :

select
H3.ParentLabel as NIVEAU0,
H2.ParentLabel as NIVEAU1,
H1.ParentLabel as NIVEAU2,
H.ParentLabel as NIVEAU3 ,
H.ChildLabel AS NIVEAU4
from Dim_Hierarchies H
left OUTER JOIN Dim_Hierarchies H1
ON H.ParentId = H1.ChildId
and H.ParentCode = H1.ChildCode
LEFT OUTER JOIN Dim_Hierarchies H2
ON H1.ParentId = H2.ChildId
and H1.ParentCode = H2.ChildCode
LEFT OUTER JOIN Dim_Hierarchies H3
ON H2.ParentId = H3.ChildId
and H2.ParentCode = H3.ChildCode

Celle-ci me retourne bien les lignes avec les 5 niveaux quand tout est renseigné mais il y a des lignes ou il n'y a que 2 ou 3 niveaux renseignés et les niveaux supérieurs sont NULL alors que je veux que ces informations remontent d'un ou plusieurs niveaux.

Résultat de ma requête:
niv 0 niv1 niv2 Niv3 Niv4

NULL NULL NULL AAA Other t
NULL NULL NULL BBB Dev eco
NULL NULL AAA Dev eco yyyy
NULL AAA Dev eco yyyy ZZZZ
AAA Dev eco Dev eco1 Dev eco3 XXXX

Ce que je cherche à obtenir c que le niveau 3 et 4 de ma première ligne soit au niveau 0 et 1 car il n'y a pas de hierarchie en dessous et pareil pour la ligne 2, 3 et 4

merci

Intéressez vous aux CTE récursives pour faire cela. Commencez par lire l'article que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/s...te-recursives/

A +

Bonjour,

Merci. J'ai pris exemple sur les CTE mais j'ai un problème sur les datas type pour cette requête.
Je ne comprends ou se trouve le problème car je passe par un CAST qui devrait résoudre le pb mais cela ne fonctionne toujours pas.

Voici la requête:

WITH H ([CountryId],[Data], [Level],[path_name]) AS
( SELECT A.[ParentId] AS CountryId
, A1.[Label] as Data
,0 AS [Level]
,CAST(''AS VARCHAR(MAX))as path_name
FROM [dbo].[Dim_tableA ] A
INNER JOIN tableA1 A1
ON A.[ParentId] = A1.[Id]
WHERE NOT EXISTS (
SELECT *
FROM [dbo].[Dim_tableC] C
WHERE C.[ChildId] = A.[ParentId]
)
GROUP BY A1.[Label], A.[ParentId]
UNION ALL
SELECT A.[ChildId] AS CountryId
,H.[Data]
,H.[Level] + 1 AS [Level]
, CAST(H.[path_name] AS VARCHAR (MAX)) + CAST(H.[Data] AS VARCHAR(MAX))
FROM [dbo].[Dim_table] A
INNER JOIN H
ON H.[CountryId] = A.[ParentId]
)
select *
from H
order by H.path_name, CountryId

message d'erreur:
Types don't match between the anchor and the recursive part in column "path_name" of recursive query "H".

J'ai essayé avec des CAST sur les champs Data sur le premier SELECT du CTE mais rien ne résoud le pb

Merci pour votre aide...

Balises code svp

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
 
WITH H ( [CountryId]
        , [Data]
        , [Level]
        , [path_name] ) AS
(
SELECT A.[ParentId] AS CountryId
     , A1.[Label] AS Data
     , 0 AS [Level]
     , CAST(''AS VARCHAR(MAX)) AS path_name
FROM [dbo].[Dim_tableA ] A
INNER JOIN tableA1 A1
   ON A.[ParentId] = A1.[Id]
WHERE NOT EXISTS (
                  SELECT *
                  FROM [dbo].[Dim_tableC] C
                  WHERE C.[ChildId] = A.[ParentId]
                  )
GROUP BY A1.[Label], A.[ParentId]
UNION ALL
SELECT A.[ChildId] AS CountryId
     , H.[Data]
     , H.[Level] + 1 AS [Level]
     , CAST( H.[path_name] AS VARCHAR (MAX)) + CAST(H.[Data] AS VARCHAR(MAX))
FROM [dbo].[Dim_table] A
INNER JOIN H
   ON H.[CountryId] = A.[ParentId]
)
 
SELECT *
FROM H
order by H.path_name, CountryId

Et un minimum d'indentation ça ne fait pas de mal

C'est le fait d'ajouter la concaténation avec le champs H.[Data] qui semble poser problème...

Code:

CAST( H.[path_name] AS VARCHAR (MAX)) + CAST(H.[DATA] AS VARCHAR(MAX))

récrivez :

Code:

H.[path_name] + H.[DATA]

si tant est que DATA soit du littéral.

A +

[CLOSED]

Bonjour,

H.[DATA] est en varchar...
Je laisse tomber et essayer de faire la hierarchie via RS

Merci

Citation:

Envoyé par sandmil

Bonjour,

H.[DATA] est en varchar...
Je laisse tomber et essayer de faire la hierarchie via RS

Merci

Ceci n'est pas une bonne solution et je doute que RS s'en sorte d'ailleurs. Essayez de continuer à investiguer la piste des CTEs; cela en vaut la peine.

J'ai été confronté, tout comme vous, à un problème de hiérarchie et après plusieurs essais; j'ai pû le faire avec une CTE.

Bonjour,

Etant donné que j'ai le ParentId, ChildId et le Level, j'ai regardé du côté de Reporting Services (car de toute facon je dois afficher cette hierarchie avec RS).
J'ai trouvé sur le BooksOnline "How to create a Recusive Hierarchy Group"
avec les expressions de Group mais je n'arrive à faire que sur 2 niveaux
ParentId/ChildId.
Level 1 ---- Level2
Level 2 ---- Level3
Level3 ---- Level 4

Je n'arrive pas à faire :

Level 1
----- Level2
------Level3
------Level4

Merci

[RESOLU] Requete hierarchique

Bonjour,

j'ai trouvé pour la CTE.

J'ai modifié la première partie de la requête :

' ' + CAST(A1.Label as varchar(max)) AS path_name

Merci

Super ! Tu penses au tag :resolu: ?

Bonjour Sandrine

Voici mon SQL, je mets en rouge ce que je vais expliquer ensuite :
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 -- Create a "Common Table Expression" (aka CTE) in order to built the metric's hierarchy WITH CTE_HierarchyMetrics ([Level], ParentID, MemberID, [Name], [Label], [Order], [Frequency], Normalisation, Leaf, Display, KPI) AS ( -- Root (level 0) : take every KPIs SELECT 0 As [Level], Cast(ROW_NUMBER() OVER (ORDER BY [Order] ASC) As varchar(MAX)) AS ParentID, MemberID, [Name], [Label], [Order], [Frequency], Normalisation, Leaf, Display, KPI FROM [MaTable].[dbo].[D_Metrics] WHERE ([Hierarchy] IS NULL) AND ([Label] = 'KPIs') UNION ALL -- Recursive member definition : the other levels SELECT [Level]+1 As [Level], C.ParentID + '.' + Cast(ROW_NUMBER() OVER (ORDER BY D.[Order]) As varchar(MAX)) AS ParentID, D.MemberID, D.[Name], D.[Label], D.[Order], D.[Frequency], D.Normalisation, D.Leaf, D.Display, D.KPI FROM [MaTable].[dbo].[D_Metrics] D INNER JOIN CTE_HierarchyMetrics As C ON D.Hierarchy = C.[Label] ) -- Populate the TempMeasure table with the metric's hierarchy SELECT DISTINCT [Level], ParentID, MemberID, [Name], [Label], [Order], [Frequency], Normalisation, Leaf, Display, KPI FROM CTE_HierarchyMetrics As C LEFT OUTER JOIN A_Fedict_SC_MeasureGroup As A ON A.Metrics_MemberID = C.MemberID WHERE (Level > 0) ORDER BY ParentID ASC
Et voici ce que cela donne :

Level ParentID MemberID Name
1 1.1 42178 Front Office Program
2 1.1.1 42179 Web Content Management
3 1.1.1.1 42180 Performance WCMS Websites
4 1.1.1.1.1 42184 Saturation WCMS Websites
4 1.1.1.1.2 42185 Availability WCMS Websites
5 1.1.1.1.2.1 42186 % Availability WCMS Websites
6 1.1.1.1.2.1.1 42187 % Availability Business
6 1.1.1.1.2.1.2 42188 % Availability Off-Peak Websites
5 1.1.1.1.2.2 42189 Evolution Failures WCMS Websites
4 1.1.1.1.3 42190 Client Satisfaction WCMS Websites
5 1.1.1.1.3.1 42191 Score Survey WCMS Websites
5 1.1.1.1.3.2 42192 Evolution Complaints WCMS Websites

Grâce à la colonne Level ainsi définie, je peux faire une formule en SSRS qui va ajouter des espaces à la gauche de mon titre : au moment d'afficher le titre (colonne Name) sur mon rapport, j'ai un truc du genre "=Space((Fields!Level.Value-1)*5) & Fields!Name.Value" c-à-d que j'ajoute des espaces à la gauhe du titre en fonction de son niveau.

Ainsi, j'obtiens un semblant d'indentation sur mon rapport imprimé.

Bonjour,

Alors, vous n'utilisez qu'une seule colonne pour imprimer tous les niveaux de hierarchies ?

Oui, effectivement. Avec l'astuce que je mentionne en SSRS pour avoir un niveau "simulé" d'indentation de mes titres.

Bonjour,

J'ai réussi à mettre en place cette facon d'afficher mais me voici confronter à un problème.
Cette solution fonctionne si on veut un affichage statique mais dans mon cas, les utilisateurs veulent avoir la possible de "déplier" les hierarchies.

L'option "Togle Item" sous SSRS ne semble pas fonctionner correctement avec ce style d'affichage. Des idées ??