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 person ‘Origin’, 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

;