October 13, 2012 at 9:29 am
I have hierarchical data structure with [State] as top level, [Region] as intermediate level and [Branch] as leaf level.
Region can have either [Region] as parent or [State]. ie hierarchy can have multilevel region.
Ex1. S1 -> R1 -> R2 -> B1
Ex2. S2-> R4 -> B2
Ex3. S3-> B3
Tables
State
StateID, Name, ProfileID
Region
RegionID, Name, Parent_RegionID, StateID, ProfileID
Branch
BranchID, Name,RegionID,StateID,ProfileID
Profile
ProfileID,IsGrp1_inherited,Grp1_V1,Grp1_V2,IsGrp2_inherited,Grp2_V2
The requirement is to get all branch’s Profile details(Grp1_V1, Grp1_V2, Grp2_V2) based the settings in the resp group flags (IsGrp1_inherited, IsGrp2_inherited). If IsGrp1_inherited = 1 then the profile details should be from its parent. ie from hierarchy Ex1: If IsGrp1_inherited = 1 then B1’s Grp1 profile details should from R1 and if R1’s IsGrp1_inherited = 1 then B1’s Grp1 profile details should from R2
The first way to get this is to create UDF for each values and use it in the select statement as,
Select
BranchID, Name,
Grp1_V1 = case when IsGrp1_inherited = 0 then Grp1_V1
else dbo.udf_getInheritedGrp_V1(BranchID) end,
Grp1_V2 = case when IsGrp1_inherited = 0 then Grp1_V2
else dbo.udf_getInheritedGrp_V2(BranchID) end,
..so on
From Branch
But the actual Profile table has around 30 groups and 80 fields. From the above approach the udf is called for all values if inherited which is more cost effective and affect the performance.Also its tedious to create 80 udf’s.
The other plan is to create udf for each group and not for each values. Ie the function returns inherited values of resp grp with seperators and parse each value in the outer query as below.
select BranchID, Name,
Grp1_V1 = (PARSE value from Grp1),
Grp1_V2 = (PARSE value from Grp1), so on
FROM(
Select
BranchID, Name,
Grp1 = case when IsGrp1_inherited = 0 then Grp1_V2
else dbo.udf_getInheritedGrp1(BranchID) end,
..so on
From Branch
)
In this case the number of function call is redused and improves perfomance. Also udf is created only for each groups and not for each values.
I’m proceeding with the 2nd option.
Please let me know if there is any other effective way for my requirement.
Thanks in advance
Gopi
October 13, 2012 at 6:26 pm
Use a CTE to scan hierarchy in a single tsql command. You need only one function or procedure.
If you provide a sample data (TSQL script) and expected results, you might get a direct solution.
October 14, 2012 at 12:26 am
Hi,
Thanks for your reply. Pls see the ddl scripts and expected output below.
CREATE TABLE [State](StateID varchar(10), Name varchar(50),ProfileID int)
CREATE TABLE Region (RegionID varchar(10), Name varchar(50),Parent_RegionID varchar(10), StateID varchar(10), ProfileID int)
CREATE TABLE Branch (BranchID varchar(10), Name varchar(50),RegionID varchar(10), StateID varchar(10), ProfileID int)
CREATE TABLE [Profile] (ProfileID INT, Grp1_Inherited BIT, Grp1_V1 VARCHAR(10),Grp1_V2 VARCHAR(10),
Grp2_inherited BIT, Grp2_V1 VARCHAR(10))
INSERT INTO [State] VALUES ('S1','State1',1),('S2','State2',2)
INSERT INTO Region VALUES('R1','Region1',NULL,'S1',3),('R2','Region2',NULL,'S1',4),('R3','Region3','R2','S1',5)
INSERT INTO Branch VALUES('B1','Branch1','R3','S1',6),('B2','Branch2','R3','S1',7),('B3','Branch3','R2','S1',8)
INSERT INTO [Profile] VALUES (1,'0','AAA','BBB','0','CCC'),(2,'0','DDD','EEE','0','FFF'),(3,'0','E2F4','SS','1','ZZZ')
,(4,'1','P8','D3','0','E3'),(5,'0','FG4','DE4','1','KL9'),(6,'0','Q34','R34','0','GH5')
,(7,'1',null,null,'1',null),(8,'0','HJ7','HH3','1','KL3')
Expected Ouput
Name Grp1_V1 Grp1_V2 Grp2_V1
Branch1 Q34 R34 GH5
Branch2 FG4 DE4 CCC
Branch3 HJ7 HH3 E3
October 14, 2012 at 4:20 pm
Your profile table and result do not match at Branch2 and Branch3 on field Grp2_V1.
I'm not sure what are you trying to achieve, but here is how to go through this hierarchy in a single CTE command:
;WITH cte as
(SELECT CurrentId = s.StateID,
Level = 1,
Id1 = s.StateID,
Id2 = convert(varchar(10), null),
Id3 = convert(varchar(10), null)
from State s
--
union all
--
select -- get regions below
CurrentId = r.RegionID,
Level = c.Level + 1,
Id1 = c.Id1,
Id2 = case when c.Level+1 < 2 then null when c.Level+1 = 2 then r.RegionID else c.Id2 END,
Id3 = case when c.Level+1 < 3 then null when c.Level+1 = 3 then r.RegionID else c.Id3 END
from region r
join cte c
on r.StateID = c.CurrentId and r.Parent_RegionID is null -- under the state
or
r.Parent_RegionID = c.CurrentId -- under the other region
)
--select * from cte --<<< Try it
select c.Id1, c.Id2, c.Id3, b.Name, b.ProfileID
from cte c
join Branch b on b.RegionID = c.CurrentId
From there, you can join with profiles and get the codes you want.
HTH,
Vedran
October 14, 2012 at 8:41 pm
Hi,
Thanks for your comment.
I think my question is not clear. Branch2 ProfileID is 7. If You look at profile table @ ProfileID 7, Grp2_inherited = 1. It means all values under Grp2 should be inherited from its parent. B2 parent is R3 and R3's profileID is 5. And again ProfileID 5 has Grp2_inherited = 1. So Grp2 value should be inherited from R3's parent which is S1 and it's profileID is 1 which has Grp2_V1 = 'CCC' (Root level will always have Grp2_inherited = 0) . Similarly for others.
Hence the values categorised in each group should be based on the respective group inherited flag. Its not like whole profile is inherited.
Thanks
Gopi
October 15, 2012 at 11:37 am
Try this. No loops or recursion.
SELECT
b.BranchID
,(CASE
WHEN b1.Grp1_Inherited = 1 THEN
CASE
WHEN r1.Grp1_Inherited = 1 THEN s1.Grp1_V1
ELSE r1.Grp1_V1
END
ELSE b1.Grp1_V1
END) AS Grp1_V1
,(CASE
WHEN b1.Grp1_Inherited = 1 THEN
CASE
WHEN r1.Grp1_Inherited = 1 THEN s1.Grp1_V2
ELSE r1.Grp1_V2
END
ELSE b1.Grp1_V2
END) AS Grp1_V2
,(CASE
WHEN b1.Grp2_Inherited = 1 THEN
CASE
WHEN r1.Grp2_Inherited = 1 THEN s1.Grp2_V1
ELSE r1.Grp2_V1
END
ELSE b1.Grp2_V1
END) AS Grp2_V1
FROM
dbo.State AS s
INNER JOIN
dbo.Region AS r
ON s.StateID = r.StateID
INNER JOIN
dbo.Branch AS b
ON r.RegionID = b.RegionID
INNER JOIN
dbo.Profile AS s1
ON s.ProfileID = s1.ProfileID
INNER JOIN
dbo.Profile AS r1
ON r.ProfileID = r1.ProfileID
INNER JOIN
dbo.Profile AS b1
ON b.ProfileID = b1.ProfileID
ORDER BY
b.BranchID
October 15, 2012 at 12:08 pm
Hi Steve,
There can be multiple intermediate hierarchy. There can be n number of Region levels as below
C1-> R1->R2->R3->R4->B1
C2->R5->B2
Thanks
Gopi
October 15, 2012 at 3:08 pm
Then you are probably stuck with using a CTE. Perhaps you can compare the code I offered with the CTE example above to see how you can do a recursive loop through the data while keeping the inheritance relationships correct.
The best primer I've seen for understanding and creating a CTE is here:
http://www.4guysfromrolla.com/webtech/071906-1.shtml
October 15, 2012 at 8:16 pm
I have been using CTE in many cases. But this is a special scenario where we can't use CTE. If you could provide then that will be more helpful.;-)
October 15, 2012 at 11:26 pm
Cte or while loop. Why you can't use cte? You have sql 2000?
October 16, 2012 at 8:19 am
Using SQL Server 2008.
I mean to say we can't use CTE for my requirement since this is not gonna be single column traverse in tree heirarchy. Request to read the Profile table, Hierarchy in the ddl and the output to get clear idea on my requirement. Whichever group is inherited only those list of field value has to be inherited from its parent. But there are also customized group that is directly pulled.
Please let me know if you need more explanation for my req.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply