Uniquely defined Hierarchical 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)

Uniquely defined Hierarchical Data ...

Author

Topic

LaurieCox

158 Posts

Posted - 2007-09-04 : 13:29:12

I am working with data from an application that uses the cache database (of which I know nothing about). Some of the data is exposed in SQL Server through a linked server. I have been tasked with creating a report from one of their data structures. This will be used as a way for the user to search the data. The presentation layer will be done in Reporting Services (which I am just starting to learn).

First of all a description of the data:

The data is a tree structure made up of Libraries, Problems, Goals, Objectives and Interventions (with libraries having highest rank and interventions having lowest rank). A node can link to any node of a higher rank. A node can link to multiple parents.

Here is an example of two libraries (note they share some children):


Library 1 (TestingLinking1)
	Problem1 abc
		Goal1 abc
			Objective1 adf
				Intervention 1 text
	Problem5 def
		Goal2 axy
			Intervention 1 text
		Intervention3 ad
	Goal2 axy
		Objective2 xyz
			Intervention2 axd
	Goal4 xyz
		Intervention4 ghf
	Objective3 aml
		Intervention3 ad

Library  2 (TestingLinking2)
	Problem1 abc 
	Problem6 est
		Goal1 abc 
	Objective2 xyz
		Intervention6 dfg
	Goal2 axy
		Intervention 1 text

To run the report in Reporting Services the user will give the following information (in parameters):

One or more libraries to look in

Which level(s) of the tree to look in

The text to look for

I will do a wild card search on the text, looking in the text fields of the various levels.

Example
User requests: All Libraries and search just the Objectives and Interventions nodes for text containing the string "f"

The resulting report would look like this (it will include the full path of any node that met the criteria)


Library 1 (TestingLinking1)
	Problem1 abc
		Goal1 abc
			Objective1 adf
	Goal4 xyz
		Intervention4 ghf

Library  2 (TestingLinking2)
	Objective2 xyz
		Intervention6 dfg

The data is stored in some very "unique" structured tables (of which I have no control of). See the following post for the data structure, sample data and a description of the tables.

What I want to do is write a stored procedure that will reformat the data in such a way that the Reporting Services report is easy to write.

My initial thought would be to create a table that looked like this:


CREATE TABLE #Found_Data
 (Report_Level     int
  Item_Type        char(1),
  Item_text        ntext,
  Item_name        varchar(50))

And I would write the stored procedure to return the following for the example given above. Reporting Services would then use the Report_Level to determine how to indent each row so that it printed in a pretty tree.


Report_Level	Item_Type	Item_Name	Item_Text
1		L		TestingLinking1
2		P		Problem1	abc
3		G		Goal1	abc
4		O		Objective1	adf
1		L		TestingLinking2	
2		O		Objective2	xyz
3		I		Intervention6	dfg

Note Item_Name in the real world would not contain the node names and therefore Item_Type and Item_Name wouldn't be redundant.

So I have following questions:

Is this a good way to design the sp?

If yes can you help me write the sql needed to take the data (as described in the following post) and format it this way?

If no can you suggest a better way to do this?

Thanks,

Laurie

LaurieCox

158 Posts

Posted - 2007-09-04 : 13:29:50

Each type (Problem, Goal, Objective, and Intervention) has two tables, a data table and a link table.

Each link table includes the links to full path of the given node.

Some examples (from the sample data given below):
Goal2 with a GOALID of 438 is linked:

TestingLinking1 Library (LIBID 9)

Problem5 (PROBID 88 ) which in turn is linked to TestingLinking1.

TestingLinking2 Library (LIBID 10)

This is represented in with the following rows in the 'link' tables.
Problem_Link_Table:


ID		FACILITY	LIBID	PROBID
1.88.9		1		9	88

Goal_Link_Table:


ID		FACILITY	LIBID	PROBID	GOALID
1.438.9.0	1		9	0	438
1.438.9.88	1		9	88	438
1.438.10.0	1		10	0	438

Notes:

The app uses Facility to keep the Production, Development and Testing data separate (that is right the data for each is stored in the same tables and differentiated with this column) … so all queries must link on facility.

The ID of each row is made up of the ID columns concatenated together. For the data tables they are delimitated with the bar (|) character. For link tables they are delimitated with the dot (.) character.


/****************** library *********************/

CREATE TABLE #Library_Data
 (ID                 varchar(254),
  FACILITY           Int,
  library_year       varchar(50),
  LIBID              Int,
  Library_Name       varchar(50),
  entry_date         Datetime,
  entry_time         varchar(50),
  entry_by           varchar(50),
  effective_date     Datetime,
  expiration_date    Datetime,
  library_origin     varchar(50),
  Wiley_Library_ID   varchar(50),
  active_code        varchar(50),
  active_value       varchar(50))

INSERT INTO #Library_Data
SELECT '1||9',1,'1840',9,'TestingLinking1', '2007-08-30 00:00:00.000', 
       'Laurie Cox', 'Laurie Cox', '2007-08-29 00:00:00.000', NULL, 
       'Test', NULL, 'N', 'No' UNION ALL
SELECT '1||10',1,'1840',10,'TestingLinking2', '2007-08-30 00:00:00.000', 
       'Laurie Cox', 'Laurie Cox', '2007-08-30 00:00:00.000', NULL, 
       'Test', NULL, 'N', 'No' 

/****************** Problems *********************/

CREATE TABLE #Problem_Data
 (ID                   varchar(254),
  FACILITY             Int,
  PROBID               Int,
  prob_Text            ntext,
  Wiley_Problem_ID     varchar(50),
  suggested_diagnosis  ntext)

INSERT INTO  #Problem_Data 
SELECT '1||87',1,87,'abc', 'Problem1', NULL UNION ALL
SELECT '1||88',1,88,'def', 'Problem5', NULL UNION ALL
SELECT '1||89',1,89,'est', 'Problem6', NULL 

CREATE TABLE #Problem_Link_Data
 (ID        varchar(50),
  FACILITY  Int,
  LIBID     Int,
  PROBID    Int)

INSERT INTO  #Problem_Link_Data 
SELECT '1.87.9',1,9,87 UNION ALL
SELECT '1.88.9',1,9,88 UNION ALL
SELECT '1.87.10',1,10,87 UNION ALL
SELECT '1.89.10',1,10,89 

/****************** Goals *********************/

CREATE TABLE #Goal_Data
(ID             varchar(254),
  FACILITY      Int,
  GOALID        Int,
  goal_text     ntext,
  Wiley_Goal_ID varchar(50))

INSERT INTO  #goal_data 
SELECT '1||437',1,437,'abc', 'Goal1' UNION ALL
SELECT '1||438',1,438,'axy', 'Goal2' UNION ALL
SELECT '1||439',1,439,'xyz', 'Goal4'

CREATE TABLE #Goal_Link_Data
 (ID         varchar(50),
  FACILITY   Int,
  LIBID      Int,
  PROBID     Int,
  GOALID     Int)

INSERT INTO  #Goal_Link_Data 
SELECT '1.438.9.0',1,9,0,438 UNION ALL
SELECT '1.439.9.0',1,9,0,439 UNION ALL
SELECT '1.437.9.87',1,9,87,437 UNION ALL
SELECT '1.438.9.88',1,9,88,438 UNION ALL
SELECT '1.438.10.0',1,10,0,438 UNION ALL
SELECT '1.437.10.89',1,10,89,437 

/****************** Objectives *********************/

CREATE TABLE #Objective_Data
 (ID                 varchar(254),
  FACILITY           Int,
  OBJID              Int,
  objective_text     ntext,
  Wiley_Objective_ID varchar(50))

INSERT INTO  #Objective_Data 
SELECT '1||1269',1,1269,'adf', 'Objective1' UNION ALL
SELECT '1||1270',1,1270,'xyz', 'Objective2' UNION ALL
SELECT '1||1271',1,1271,'aml', 'Objective3' 

CREATE TABLE #Objective_Link_Data
 (ID        varchar(50),
  FACILITY  Int,
  LIBID     Int,
  PROBID    Int,
  GOALID    Int,
  OBJID     Int)

INSERT INTO  #Objective_Link_Data 
SELECT '1.1271.9.0.0',1,9,0,0,1271 UNION ALL
SELECT '1.1270.9.0.438',1,9,0,438,1270 UNION ALL
SELECT '1.1269.9.87.437',1,9,87,437,1269 UNION ALL
SELECT '1.1270.10.89.437',1,10,89,437,1270 

/****************** Interventions *********************/

CREATE TABLE #Intervention_Data
 (ID                    varchar(254),
  FACILITY              Int,
  INTID                 Int,
  intervention_text     ntext,
  Wiley_Intervention_ID varchar(50))

INSERT INTO  #Intervention_Data 
SELECT '1||1',1,1,'text', 'Intervention 1' UNION ALL
SELECT '1||2469',1,2469,'axd', 'Intervention2' UNION ALL
SELECT '1||2470',1,2470,'ad', 'Intervention3' UNION ALL
SELECT '1||2471',1,2471,'ghf', 'Intervention4' UNION ALL
SELECT '1||2472',1,2472,'dfg', 'Intervention6' UNION ALL
SELECT '1||1',1,1,'text', 'Intervention 1' UNION ALL
SELECT '1||2469',1,2469,'axd', 'Intervention2' UNION ALL
SELECT '1||2470',1,2470,'ad', 'Intervention3' UNION ALL
SELECT '1||2471',1,2471,'ghf', 'Intervention4' UNION ALL
SELECT '1||2472',1,2472,'dfg', 'Intervention6' 

CREATE TABLE #Intervention_Link_Data
 (ID        varchar(50),
  FACILITY  Int,
  LIBID     Int,
  PROBID    Int,
  GOALID    Int,
  OBJID     Int,
  INTID     Int)

INSERT INTO  #Intervention_Link_Data 
SELECT '1.2470.9.0.0.1271',1,9,0,0,1271,2470 UNION ALL
SELECT '1.2469.9.0.438.1270',1,9,0,438,1270,2469 UNION ALL
SELECT '1.2471.9.0.439.0',1,9,0,439,0,2471 UNION ALL
SELECT '1.1.9.87.437.1269',1,9,87,437,1269,1 UNION ALL
SELECT '1.2470.9.88.0.0',1,9,88,0,0,2470 UNION ALL
SELECT '1.1.9.88.438.0',1,9,88,438,0,1 UNION ALL
SELECT '1.1.10.0.438.0',1,10,0,438,0,1 UNION ALL
SELECT '1.2472.10.89.437.1270',1,10,89,437,1270,2472

LaurieCox

158 Posts

Posted - 2007-09-04 : 16:28:47

I am going to out of the office all day tomorrow (Sept 5th - Wednesday) ... So don't think I am ignoring this thread. Please ask questions if anything needs clarifying. I will get to them ASAP on Thursday morning.

I have not gotten very far since I posted this. The following queries give me all of the paths for Interventions and objectives that have an "f" in the text:


select * 
  from #Intervention_link_Data il
 where exists
         (select i.intid 
            from #Intervention_Data i
           where i.Intervention_text like '%f%'
             and i.intid = il.intid)


select * 
  from #Objective_link_Data ol
 where exists
         (select o.objid 
            from #Objective_Data o
           where o.Objective_text like '%f%'
             and o.objid = ol.objid)

Results:


ID		FACILITY	LIBID	PROBID	GOALID	OBJID	INTID
1.2471.9.0.439.0	1	9	0	439	0	2471
1.2472.10.89.437.1270	1	10	89	437	1270	2472

(2 row(s) affected)

ID		FACILITY	LIBID	PROBID	GOALID	OBJID
1.1269.9.87.437	1	9	87	437	1269

… but I am not sure where to go from here?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources