Tuesday, May 17, 2011

Hierarchical Query – Oracle 10g & SQL Server

The issue is simple.
You want a migration of your database from Oracle to SQL Server.
Hierarchical queries with parent child relationship inside the same table can be a pretty interesting area. In SQL Server 2000, we do not have any straightaway method to do so (unlike in Oracle 10g), along with customized output, but the pain is reduced in higher versions of SQL Server (SQL Server 2005 & 2008). Let us look at the basics quiet SIMPLY.
Oracle 10g
I will create the FAMILY table to explain this. However, there is a couple of assumption.
a)      The data in the FAMILY table does not belong to a person having fallen in love multiple times, resulting in multiple marriages, further resulting in multiple families. So, Sukhendu’s father and Rajkumar’s father is not the same person.
b)      Here null as parent_id means simply I am using it for this example. Definitely the Greater Grand Father also had his father.
However, be informed that Oracle can take care of a person having multiple families as well.

Person_ID
Person
Parent_ID
5
Sukhendu’sGreaterGrandFather
null
4
Sukhendu’sGreatGrandFather
5
3
Sukhendu’sGrandFather
4
2
Sukhendu’sFather
3
1
Sukhendu
2
0
Sukhendu’sBrother
2
55
Rajkumar’sGreaterGrandFather
null
44
Rajkumar’sGreatGrandFather
55
33
Rajkumar’sGrandFather
44
22
Rajkumar’sFather
33
11
Rajkumar
22
99
Rajkumar’sSister
22


LEVEL
Do have a look at this query.
SELECT person_id, LPAD (' ', LEVEL*3) ||Person as Person, parent_id, LEVEL
FROM family
START WITH parent_id IS NULL
CONNECT BY PRIOR person_id = parent_id;


Result:

Person_ID
Person
Parent_ID
LEVEL
5
Sukhendu’sGreaterGrandFather
null
1
4
      Sukhendu’sGreatGrandFather
5
2
3
         Sukhendu’sGrandFather
4
3
2
            Sukhendu’sFather
3
4
1
               Sukhendu
2
5
0
               Sukhendu’sBrother
2
5
55
Rajkumar’sGreaterGrandFather
null
1
44
      Rajkumar’sGreatGrandFather
55
2
33
         Rajkumar’sGrandFather
44
3
22
            Rajkumar’sFather
33
4
11
               Rajkumar
22
5
99
               Rajkumar’sSister
22
5


The topmost in the hierarchy gets the LEVEL 1 with its children designated as per hierarchy. LPAD introduces the blank space for tabbing the names as per LEVEL.
START WITH defines the root row(s) of the hierarchy.
CONNECT BY defines the relationship between parent and child in the hierarchy.
PRIOR is a unary operator. PRIOR can be on either side of the equality operator. It evaluates the immediately following expression for the parent row of the current row in a hierarchical query.
SYS_CONNECT_BY_PATH function gives you the path from root to node (or topmost to bottom most in the hierarchy).

SYS_CONNECT_BY_PATH

SELECT person_id, LPAD (' ', LEVEL*3) ||Person as Person, LEVEL, SYS_CONNECT_BY_PATH (person,'/') PATH
FROM family
START WITH parent_id IS NULL
CONNECT BY PRIOR person_id = parent_id;


Person_ID
Person
LEVEL
PATH
5
Sukhendu’sGreaterGrandFather
1
/ Sukhendu’sGreaterGrandFather
4
      Sukhendu’sGreatGrandFather
2
/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather
3
         Sukhendu’sGrandFather
3
/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather
2
            Sukhendu’sFather
4
/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather/ Sukhendu’sFather
1
               Sukhendu
5
/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather/ Sukhendu’sFather/
Sukhendu
0
               Sukhendu’sBrother
5
/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather/ Sukhendu’sFather/ Sukhendu’sBrother
55
Rajkumar’sGreaterGrandFather
1
/ Rajkumar’sGreaterGrandFather
44
      Rajkumar’sGreatGrandFather
2
/
Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather
33
         Rajkumar’sGrandFather
3
/
Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/
Rajkumar’sGrandFather
22
            Rajkumar’sFather
4
/
Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/
Rajkumar’sGrandFather/
Rajkumar’sFather
11
               Rajkumar
5
/
Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/
Rajkumar’sGrandFather/
Rajkumar’sFather/
Rajkumar
99
               Rajkumar’sSister
5
/
Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/
Rajkumar’sGrandFather/
Rajkumar’sFather/
Rajkumar’sSister


CONNECT_BY_ISLEAF

SELECT person, CONNECT_BY_ISLEAF ‘isLEAF?’, SYS_CONNECT_BY_PATH (person,'/') path 
from family
START WITH parent_id IS NULL
CONNECT BY PRIOR person_id = parent_id;


Person
isLEAF?
PATH
Sukhendu’sGreaterGrandFather
0
/ Sukhendu’sGreaterGrandFather
      Sukhendu’sGreatGrandFather
0
/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather
         Sukhendu’sGrandFather
0
/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather
            Sukhendu’sFather
0
/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather/ Sukhendu’sFather
               Sukhendu
1
/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather/ Sukhendu’sFather/
Sukhendu
               Sukhendu’sBrother
1
/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather/ Sukhendu’sFather/ Sukhendu’sBrother
Rajkumar’sGreaterGrandFather
0
/ Rajkumar’sGreaterGrandFather
      Rajkumar’sGreatGrandFather
0
/
Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather
         Rajkumar’sGrandFather
0
/
Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/
Rajkumar’sGrandFather
            Rajkumar’sFather
0
/
Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/
Rajkumar’sGrandFather/
Rajkumar’sFather
               Rajkumar
1
/
Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/
Rajkumar’sGrandFather/
Rajkumar’sFather/
Rajkumar
               Rajkumar’sSister
1
/
Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/
Rajkumar’sGrandFather/
Rajkumar’sFather/
Rajkumar’sSister


CONNECT_BY_ROOT
SELECT person, CONNECT_BY_ROOT personOrigin’, SYS_CONNECT_BY_PATH (person,'/') path
FROM
family
START WITH
parent_id is null
CONNECT BY PRIOR person_id = parent_id;


Person
Origin
PATH
Sukhendu’sGreaterGrandFather
Sukhendu’sGreaterGrandFather
/ Sukhendu’sGreaterGrandFather
      Sukhendu’sGreatGrandFather
Sukhendu’sGreaterGrandFather
/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather
         Sukhendu’sGrandFather
Sukhendu’sGreaterGrandFather
/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather
            Sukhendu’sFather
Sukhendu’sGreaterGrandFather
/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather/ Sukhendu’sFather
               Sukhendu
Sukhendu’sGreaterGrandFather
/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather/ Sukhendu’sFather/
Sukhendu
               Sukhendu’sBrother
Sukhendu’sGreaterGrandFather
/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather/ Sukhendu’sFather/ Sukhendu’sBrother
Rajkumar’sGreaterGrandFather
Rajkumar’sGreaterGrandFather
/ Rajkumar’sGreaterGrandFather
      Rajkumar’sGreatGrandFather
Rajkumar’sGreaterGrandFather
/
Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather
         Rajkumar’sGrandFather
Rajkumar’sGreaterGrandFather
/
Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/
Rajkumar’sGrandFather
            Rajkumar’sFather
Rajkumar’sGreaterGrandFather
/
Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/
Rajkumar’sGrandFather/
Rajkumar’sFather
               Rajkumar
Rajkumar’sGreaterGrandFather
/
Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/
Rajkumar’sGrandFather/
Rajkumar’sFather/
Rajkumar
               Rajkumar’sSister
Rajkumar’sGreaterGrandFather
/
Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/
Rajkumar’sGrandFather/
Rajkumar’sFather/
Rajkumar’sSister


CONNECT_BY_ISCYCLE

Now let us assume that, Rajkumar is the father of his real father Rajkumar’sFather (whose real name is XYZ suppose). I know it’s hard to imagine, but hypothetically this definitely happens when your father is aged and looking up to you for all kind of support, guidance and warmth you got during childhood. Then you do the parental role. If this kind of looping occurs in your table then it can be fairly achieved through the pseudocolumn, CONNECT_BY_ISCYCLE, which will evaluate to"1", in these circumstances ( due to looping inside the table as explained later), along with NOCYCLE.  Let me redefine the family table as FAMILY_1 and eliminate the Sukhendu subgroup.

UPDATE family_1
SET parent_id = 11 ~ Rajkumar
WHERE person_id = 22 ~ Rajkumar’sFather


Person_ID
Person
Parent_ID
isLOOP?
55
Rajkumar’sGreaterGrandFather
null
0
44
Rajkumar’sGreatGrandFather
55
0
33
Rajkumar’sGrandFather
44
0
22
Rajkumar’sFather
11
0
11
Rajkumar
22
1
88
Rajkumar’sDaughter
11
0
99
Rajkumar’sSister
22
0


The query for the above table is as follows:

SELECT person_id, person, parent_id, CONNECT_BY_ISCYCLE ‘isLOOP?’
FROM family_1
START WITH parent_id IS NULL
CONNECT BY NOCYCLE PRIOR person_id = parent_id;

SQL SERVER 2000

In SQL Server 2000 the derivation of a hierarchical result set is simple, but the customizations which are often required for a hierarchical display of data are very much difficult.

SELECT f1.person_id, f1.person, f1.parent_id, f2.person AS PARENT
FROM family f1
LEFT JOIN family f2 ON f1.parent_id = f2.person_id

SQL SERVER 2005

We are very much aware of Common Table Expressions (CTE) introduced in the later versions of SQL Server 2000. These work very efficiently in case of recursive queries. Although there can be much complex implementation of the CTE, the simplest can take care of the hierarchy issue with lot more customizations than in SQL Server 2000. We can address the complications later but for the time being let us just look at the query which can handle the same with the LEVEL description.           

WITH FamilyHierarchy (parent_id, person_id, person, LEVEL)
AS
(
-- Identity Description
    SELECT f.parent_id, f.person_id, f.person,
        0 AS LEVEL
    FROM family AS f
    WHERE parent_id IS NULL
    UNION ALL
-- Hierarchical Identity Description
    SELECT f.parent_id, f.person_id, f.person,
        LEVEL + 1
    FROM family AS f
    INNER JOIN FamilyHierarchy AS fa
        ON f.parent_id = fa.person_id
)
-- Statement that executes the CTE
SELECT fa.parent_id, fa.person_id, fa.person, LEVEL
FROM FamilyHierarchy AS fa
;