Hierarchial Query - Order of Data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Hierarchial Query - Order of Data

Author

Topic

imtu_174
Starting Member

9 Posts

Posted - 2005-11-02 : 16:55:58

Hi

I am using SQL Server 2005 Developer edition(Sep 05). I had an oracle hierarchial query the equivalent of which I had written in SQL Server. The problem is the order of the data is different in SQL Server.

To put in proper context :

I need

1. The root node
2. The root’s children and the children of roolt's children and so on and so forth

I get

1. The root node
2. The root’s immediate children
3. The children of the root’s immediate children
4. And so forth.

Find below the DDL, Insert script, the Hierarchial Query, the data expected to return and the actual data returned.

--------------------------------------------------------------------------
----- ---- DDL Script -------------------------------------------------
--------------------------------------------------------------------------

CREATE TABLE [sfmfg].[SFPL_MFG_BOM_TEST](

[ITEM_ID] [varchar](40) NOT NULL,

[MFG_BOM_CHG] [varchar](4) NOT NULL,

[PARENT_ITEM_ID] [varchar](40) NOT NULL,

[PARENT_MFG_BOM_CHG] [varchar](4) NOT NULL,

CONSTRAINT [SFPL_MFG_BOM_TEST_PK] PRIMARY KEY CLUSTERED

(

[ITEM_ID] ASC,

[MFG_BOM_CHG] ASC,

[PARENT_ITEM_ID] ASC,

[PARENT_MFG_BOM_CHG] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

--------------------------------------------------------------------------
----- ---- Insert Script -----------------------------------------------
--------------------------------------------------------------------------

INSERT INTO SFPL_MFG_BOM_TEST (ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ( 'SE1','A', 'N/A', 'N/A')

INSERT INTO SFPL_MFG_BOM_TEST

(ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ('MF1','A', 'SE1', 'A')

INSERT INTO SFPL_MFG_BOM_TEST

(ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ( 'CYL1','A', 'SE1', 'A')

INSERT INTO SFPL_MFG_BOM_TEST

(ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ('P1','A', 'SE1', 'A')

INSERT INTO SFPL_MFG_BOM_TEST

(ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ('TB1','A', 'MF1', 'A')

INSERT INTO SFPL_MFG_BOM_TEST

(ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ( 'BB1','A', 'MF1', 'A')

INSERT INTO SFPL_MFG_BOM_TEST

(ITEM_ID,MFG_BOM_CHG,PARENT_ITEM_ID,PARENT_MFG_BOM_CHG)

VALUES ( 'BT1','A', 'MF1', 'A')

--------------------------------------------------------------------------
----- ---- Hierarchial Query -------------------------------------------
--------------------------------------------------------------------------

WITH ParentBOM(item_id,mfg_bom_chg,parent_item_id,parent_mfg_bom_chg,Level)

AS

(

SELECT item_id,mfg_bom_chg,parent_item_id,parent_mfg_bom_chg,1 as Level

FROM sfpl_mfg_bom_test

WHERE item_id = 'SE1'

and mfg_bom_chg = 'A'

and parent_item_id = 'N/A'

and parent_mfg_bom_chg = 'N/A'

UNION ALL

SELECT c.item_id,c.mfg_bom_chg,c.parent_item_id,c.parent_mfg_bom_chg,Level+1

FROM sfpl_mfg_bom_test c INNER JOIN ParentBOM p

ON p.item_id = c.parent_item_id

AND p.mfg_bom_chg = c.parent_mfg_bom_chg

)

Select item_id,mfg_bom_chg,parent_item_id,parent_mfg_bom_chg,level

from ParentBOM

--------------------------------------------------------------------------
----- ---- Expected Data -------------------------------------------
--------------------------------------------------------------------------

item_id mfg_bom_chg parent_item_id parent_mfg_bom_chg level

---------------------------------------- ----------- ---------------------------------------- -----------------

SE1 A N/A N/A 1
CYL1 A SE1 A 2
MF1 A SE1 A 2
BB1 A MF1 A 3
BT1 A MF1 A 3
TB1 A MF1 A 3
P1 A SE1 A 2
--------------------------------------------------------------------------
----- ---- Returned Data -------------------------------------------
--------------------------------------------------------------------------

item_id mfg_bom_chg parent_item_id parent_mfg_bom_chg level

---------------------------------------- ----------- ----------------
SE1 A N/A N/A 1
CYL1 A SE1 A 2
MF1 A SE1 A 2
P1 A SE1 A 2
BB1 A MF1 A 3
BT1 A MF1 A 3
TB1 A MF1 A 3
------------------------ -----------------
Any help in this matter would be greatly appreciated.

Thanks & Regards
Imtiaz

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-04 : 05:19:12

See if these are helpful

http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm
http://www.nigelrivett.net/RetrieveTreeHierarchy.html
http://www.seventhnight.com/treestructs.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources