Explosion of tree structure with Select Query

1

Hi Team

I want to get the data from table (Tree Structure) from Parent node to till last child.
Here are the scenarios
Top Level parent may have another parent as child and this child parent may have another subchild as parent
I have to extract till path reaches to last child. There might be one level, two level...Nth Level..

I have to extract the data with select query /procedure till we reaches to last child

Here is the sample data

PARENTTYPE PARENTNAME PARENTREV CHILDTYPE CHILDNAME CHILDREV
A AAA 001 A PQR 001
A AAA 001 B XYZ 001
A AAA 001 B XYZ 002
A AAA 001 A CDE 001
A PQR 001 A BCD 001
A PQR 001 B MNO 001
A PQR 001 B MNO 002
A PQR 001 B IJK 001
A CDE 001 B ZXY 001
A BCD 001 B BHO 001
B XYZ 001 B JKL 001
B XYZ 001 B JKL 002
B MNO 002 B ABP 001
2

So what do you want the output to look like and how should the parent and child revs be used?

Also, if you really want help, covert your columnized data above to INSERT or VALUES statements and provide use with readily consumable code. Be sure to include the CREATE TABLE statement.

Otherwise, use a recursive CTE (look it up in Books Online) for how to do one. You can also see the following article for an explanation on how to solve a similar problem and then add you own slant to it to handle your data above.
http://www.sqlservercentral.com/articles/T-SQL/72503/

3

Here I have given one Parent with Different childs. My Target is I want a query /Procedure to extract the data for each parent irrespective of the sub levels.

Thanks in advance

Shreek

4

Heh... here I have given an answer that works for that. All you have to do is read the article. Also, you've not explained what you want to do with the revision columns. If you cannot describe the answer, we can't help you code the answer. :wink:

1 Like
5

In PLM each object is identified by TYPE,NAME and REV. so the combination these three is identified as one parent. So I have given both Parent and Child Details. My requirement is If the user want has given one Parent Type,name and rev , I need to extract all the parent,sub parent and child details till the end with in the tree structure. Some Parents may have 1 level and some some may have 3 levels down and some parent may have 7 levels down in the tree structure. So I need a standard query or procedure to get the details of parent/tree explosion .

Hope I am able to explain the requirement here.

Thank u..
Shreek

6

Yep... I get all of that. The key is that if you have 2 revs of a child in the downline for a given parent, which rev of the child do you want to return? The lowest rev, the highest rev, or all the revs. In the latter remember that will cause an "explosion" of other child nodes because each rev of a given node had children.

While we're at it, could you put your example data into a readily consumable format for loading into a table so we can start working on this problem? Thanks.

7

CREATE TABLE TREESTRUCT( PARENTTYPE VARCHAR(10)
,PARENTNAME VARCHAR(50)
,PARENTREV VARCHAR(5)
,CHILDTYPE VARCHAR(10)
,CHILDNAME VARCHAR(50)
,CHILDREV VARCHAR(5));

INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'510H0007,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'510H0012,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'520H0010,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'520H0019,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'520H0116,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'520H0151,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'841490,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'844088,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'844089,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'844112-001,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'844112-002,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'844112-003,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'844112-004,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'844112-005,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'844112-006,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'844112-007,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'844112-008,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'844112-009,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844185,'A,'TYPE2,'698300P0005,'B');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844185,'A,'TYPE2,'698300P0033,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844185,'A,'TYPE2,'698300P0035,'B');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844185,'A,'TYPE2,'698300P0036,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844185,'A,'TYPE2,'698300P0077,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844185,'A,'TYPE2,'698300P0080,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844185,'A,'TYPE2,'698300P0175,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844185,'A,'TYPE2,'698300P0191,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844185,'A,'TYPE2,'844185-001,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE1,'844078,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE1,'844185,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'10P053,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'382A9515,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'390A1773,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'390A1783,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'390A3742,'B');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'397A1144,'B');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'397A1151,'B');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'397A1153,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'397A1158,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'399A2838,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'500H0007,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'500H0021,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844112,'A,'TYPE2,'500H0091,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'386A6600,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'390A2136,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'844078-001,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'844078-002,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'844078-003,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'844078-004,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'844078-005,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'844078-006,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'844078-007,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'844078-008,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'844078-009,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'844078-010,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'844078-011,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'844078-012,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'844078-013,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'844078-014,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'844078-015,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'844078-016,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'844078-017,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'844078-018,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'844078-019,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'844078-020,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'844078-021,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'844078-022,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'844078-023,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'844078-024,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'844078-025,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'844078-026,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'844078-027,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'844078-028,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'844078-029,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'J01420,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'844078,'A,'TYPE2,'J04465,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'701336,'C,'TYPE2,'701336-001,'C');
INSERT INTO TREESTUCT VALUES ('TYPE1,'701336,'C,'TYPE2,'701336-002,'C');
INSERT INTO TREESTUCT VALUES ('TYPE1,'701336,'C,'TYPE2,'701336-003,'C');
INSERT INTO TREESTUCT VALUES ('TYPE1,'701336,'C,'TYPE2,'701336-004,'C');
INSERT INTO TREESTUCT VALUES ('TYPE1,'701336,'C,'TYPE2,'701336-005,'C');
INSERT INTO TREESTUCT VALUES ('TYPE1,'701336,'C,'TYPE2,'701336-006,'C');
INSERT INTO TREESTUCT VALUES ('TYPE1,'701336,'C,'TYPE2,'701336-007,'C');
INSERT INTO TREESTUCT VALUES ('TYPE1,'701336,'C,'TYPE2,'701336-008,'C');
INSERT INTO TREESTUCT VALUES ('TYPE1,'701336,'C,'TYPE2,'701336-009,'C');
INSERT INTO TREESTUCT VALUES ('TYPE1,'701336,'C,'TYPE2,'701336-010,'C');
INSERT INTO TREESTUCT VALUES ('TYPE1,'701336,'C,'TYPE2,'701336-011,'C');
INSERT INTO TREESTUCT VALUES ('TYPE1,'701336,'C,'TYPE2,'701336-012,'C');
INSERT INTO TREESTUCT VALUES ('TYPE1,'701336,'C,'TYPE2,'701336-013,'C');
INSERT INTO TREESTUCT VALUES ('TYPE1,'701336,'C,'TYPE2,'701336-014,'C');
INSERT INTO TREESTUCT VALUES ('TYPE1,'701336,'C,'TYPE2,'701336-017,'C');
INSERT INTO TREESTUCT VALUES ('TYPE1,'701336,'C,'TYPE2,'83891,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'701336,'C,'TYPE2,'83892,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'701336,'C,'TYPE2,'J00509,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE1,'701209,'02');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE1,'813013,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE1,'813014,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE1,'813015,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE1,'813016,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE1,'813255,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE2,'390A1532,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE2,'390A2873,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE2,'500H0125,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE2,'500H0126,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE2,'500H0127,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE2,'500H0128,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE2,'500H0129,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE2,'510H0023,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813013,'A,'TYPE2,'813013-001,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813013,'A,'TYPE2,'813013-002,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813013,'A,'TYPE2,'813013-003,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813013,'A,'TYPE2,'813013-005,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813013,'A,'TYPE2,'813013-006,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813013,'A,'TYPE2,'813013-009,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813014,'A,'TYPE2,'813014-001,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813014,'A,'TYPE2,'813014-002,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813014,'A,'TYPE2,'813014-003,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813014,'A,'TYPE2,'813014-004,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813014,'A,'TYPE2,'813014-005,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813014,'A,'TYPE2,'813014-006,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813014,'A,'TYPE2,'813014-007,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813014,'A,'TYPE2,'813014-008,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813014,'A,'TYPE2,'813014-009,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813014,'A,'TYPE2,'813014-010,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813014,'A,'TYPE2,'813014-011,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813014,'A,'TYPE2,'813014-012,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813014,'A,'TYPE2,'813014-013,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813014,'A,'TYPE2,'813014-014,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813014,'A,'TYPE2,'813014-015,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813014,'A,'TYPE2,'813014-016,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813014,'A,'TYPE2,'813014-017,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813015,'A,'TYPE2,'813015-001,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813015,'A,'TYPE2,'813015-002,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813015,'A,'TYPE2,'813015-003,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813015,'A,'TYPE2,'813015-004,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813015,'A,'TYPE2,'813015-005,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813015,'A,'TYPE2,'813015-006,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813016,'A,'TYPE2,'813016-001,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813016,'A,'TYPE2,'813016-002,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813016,'A,'TYPE2,'813016-003,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813255,'A,'TYPE2,'377A1024P0000,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813255,'A,'TYPE2,'545-8,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813255,'A,'TYPE2,'79908,'H');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813255,'A,'TYPE2,'813255-001,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813255,'A,'TYPE2,'813255-002,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813255,'A,'TYPE2,'813255-003,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813255,'A,'TYPE2,'813255-004,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813255,'A,'TYPE2,'813255-005,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813255,'A,'TYPE2,'813255-006,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813255,'A,'TYPE2,'813255-007,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813255,'A,'TYPE2,'813255-008,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813255,'A,'TYPE2,'813255-009,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813255,'A,'TYPE2,'813255-010,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813255,'A,'TYPE2,'813255-011,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813255,'A,'TYPE2,'813255-012,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813255,'A,'TYPE2,'89931,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813255,'A,'TYPE2,'91699,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813255,'A,'TYPE2,'J00390,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813255,'A,'TYPE2,'J00524,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813255,'A,'TYPE2,'J00533,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813255,'A,'TYPE2,'J00619,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813255,'A,'TYPE2,'J00755,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813255,'A,'TYPE2,'J00762,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813255,'A,'TYPE2,'J00779,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813255,'A,'TYPE2,'J02360,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813255,'A,'TYPE2,'J04155,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813016,'A,'TYPE2,'813016-004,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813016,'A,'TYPE2,'813016-005,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813016,'A,'TYPE2,'813016-006,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813016,'A,'TYPE2,'813016-007,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE2,'510H0024,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE2,'520H0022,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE2,'520H0024,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE2,'520H0030,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE2,'520H0032,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE2,'520H0035,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE2,'530H0002,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE2,'530H0016,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE2,'530H0037,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE2,'530H0047,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE2,'698300P0070,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE2,'780-1812,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE2,'813012-001-01,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE2,'813012-001-02,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE2,'813022,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE2,'91699,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE2,'CG-1D,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE2,'CG-1G,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE2,'CG-1J,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE2,'CG-1P,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'813012,'A,'TYPE2,'J01849,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'701209,'02,'TYPE1,'701336,'C');
INSERT INTO TREESTUCT VALUES ('TYPE1,'701209,'02,'TYPE2,'2028511,'1');
INSERT INTO TREESTUCT VALUES ('TYPE1,'701209,'02,'TYPE2,'334A5180,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'701209,'02,'TYPE2,'334A5537,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'701209,'02,'TYPE2,'92316,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'701209,'02,'TYPE2,'92318,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'701209,'02,'TYPE2,'CG-1Q,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'701209,'02,'TYPE2,'J00201,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'701209,'02,'TYPE2,'J00395,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'701209,'02,'TYPE2,'J00397,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'701209,'02,'TYPE2,'J00407,'A');
INSERT INTO TREESTUCT VALUES ('TYPE1,'701209,'02,'TYPE2,'J00532,'0');

Here is the sample data. there two parents and One Parent is 844112, rev 'A' , so we need to do the explosion till it reaches to last child

if you have 2 revs of a child in the downline for a given parent, which rev of the child do you want to return?

Need to have query for the both the cases. If we have parent is in released state we need to take child with maxrev and in other cases we need all the revisions of child.(future requirement)

Shreek

8

You should get into the habit of trying the code you create before posting it. Try running what you posted and see what I mean.

9

hi shreek

if you are a newbie to sql
tough to know how to do things

keeping that in my mind
i have done this for you

drop create data 
use tempdb 
go 

drop table TREESTRUCT
go 
 
CREATE TABLE TREESTRUCT
( PARENTTYPE VARCHAR(10)
,PARENTNAME VARCHAR(50)
,PARENTREV VARCHAR(5)
,CHILDTYPE VARCHAR(10)
,CHILDNAME VARCHAR(50)
,CHILDREV VARCHAR(5));

INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','510H0007','A');
go 

INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','510H0012','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','520H0010','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','520H0019','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','520H0116','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','520H0151','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','841490','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','844088','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','844089','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','844112-001','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','844112-002','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','844112-003','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','844112-004','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','844112-005','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','844112-006','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','844112-007','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','844112-008','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','844112-009','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844185,'A','TYPE2','698300P0005','B');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844185,'A','TYPE2','698300P0033','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844185,'A','TYPE2','698300P0035','B');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844185,'A','TYPE2','698300P0036','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844185,'A','TYPE2','698300P0077','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844185,'A','TYPE2','698300P0080','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844185,'A','TYPE2','698300P0175','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844185,'A','TYPE2','698300P0191','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844185,'A','TYPE2','844185-001','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE1','844078','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE1','844185','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','10P053','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','382A9515','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','390A1773','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','390A1783','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','390A3742','B');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','397A1144','B');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','397A1151','B');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','397A1153','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','397A1158','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','399A2838','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','500H0007','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','500H0021','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844112,'A','TYPE2','500H0091','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','386A6600','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','390A2136','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','844078-001','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','844078-002','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','844078-003','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','844078-004','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','844078-005','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','844078-006','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','844078-007','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','844078-008','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','844078-009','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','844078-010','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','844078-011','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','844078-012','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','844078-013','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','844078-014','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','844078-015','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','844078-016','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','844078-017','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','844078-018','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','844078-019','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','844078-020','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','844078-021','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','844078-022','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','844078-023','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','844078-024','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','844078-025','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','844078-026','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','844078-027','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','844078-028','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','844078-029','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','J01420','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',844078,'A','TYPE2','J04465','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',701336,'C','TYPE2','701336-001','C');
INSERT INTO TREESTRUCT VALUES ('TYPE1',701336,'C','TYPE2','701336-002','C');
INSERT INTO TREESTRUCT VALUES ('TYPE1',701336,'C','TYPE2','701336-003','C');
INSERT INTO TREESTRUCT VALUES ('TYPE1',701336,'C','TYPE2','701336-004','C');
INSERT INTO TREESTRUCT VALUES ('TYPE1',701336,'C','TYPE2','701336-005','C');
INSERT INTO TREESTRUCT VALUES ('TYPE1',701336,'C','TYPE2','701336-006','C');
INSERT INTO TREESTRUCT VALUES ('TYPE1',701336,'C','TYPE2','701336-007','C');
INSERT INTO TREESTRUCT VALUES ('TYPE1',701336,'C','TYPE2','701336-008','C');
INSERT INTO TREESTRUCT VALUES ('TYPE1',701336,'C','TYPE2','701336-009','C');
INSERT INTO TREESTRUCT VALUES ('TYPE1',701336,'C','TYPE2','701336-010','C');
INSERT INTO TREESTRUCT VALUES ('TYPE1',701336,'C','TYPE2','701336-011','C');
INSERT INTO TREESTRUCT VALUES ('TYPE1',701336,'C','TYPE2','701336-012','C');
INSERT INTO TREESTRUCT VALUES ('TYPE1',701336,'C','TYPE2','701336-013','C');
INSERT INTO TREESTRUCT VALUES ('TYPE1',701336,'C','TYPE2','701336-014','C');
INSERT INTO TREESTRUCT VALUES ('TYPE1',701336,'C','TYPE2','701336-017','C');
INSERT INTO TREESTRUCT VALUES ('TYPE1',701336,'C','TYPE2','83891','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',701336,'C','TYPE2','83892','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',701336,'C','TYPE2','J00509','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE1','701209','02');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE1','813013','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE1','813014','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE1','813015','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE1','813016','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE1','813255','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE2','390A1532','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE2','390A2873','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE2','500H0125','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE2','500H0126','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE2','500H0127','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE2','500H0128','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE2','500H0129','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE2','510H0023','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813013,'A','TYPE2','813013-001','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813013,'A','TYPE2','813013-002','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813013,'A','TYPE2','813013-003','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813013,'A','TYPE2','813013-005','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813013,'A','TYPE2','813013-006','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813013,'A','TYPE2','813013-009','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813014,'A','TYPE2','813014-001','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813014,'A','TYPE2','813014-002','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813014,'A','TYPE2','813014-003','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813014,'A','TYPE2','813014-004','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813014,'A','TYPE2','813014-005','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813014,'A','TYPE2','813014-006','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813014,'A','TYPE2','813014-007','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813014,'A','TYPE2','813014-008','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813014,'A','TYPE2','813014-009','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813014,'A','TYPE2','813014-010','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813014,'A','TYPE2','813014-011','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813014,'A','TYPE2','813014-012','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813014,'A','TYPE2','813014-013','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813014,'A','TYPE2','813014-014','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813014,'A','TYPE2','813014-015','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813014,'A','TYPE2','813014-016','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813014,'A','TYPE2','813014-017','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813015,'A','TYPE2','813015-001','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813015,'A','TYPE2','813015-002','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813015,'A','TYPE2','813015-003','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813015,'A','TYPE2','813015-004','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813015,'A','TYPE2','813015-005','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813015,'A','TYPE2','813015-006','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813016,'A','TYPE2','813016-001','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813016,'A','TYPE2','813016-002','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813016,'A','TYPE2','813016-003','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813255,'A','TYPE2','377A1024P0000','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813255,'A','TYPE2','545-8','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813255,'A','TYPE2','79908','H');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813255,'A','TYPE2','813255-001','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813255,'A','TYPE2','813255-002','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813255,'A','TYPE2','813255-003','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813255,'A','TYPE2','813255-004','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813255,'A','TYPE2','813255-005','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813255,'A','TYPE2','813255-006','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813255,'A','TYPE2','813255-007','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813255,'A','TYPE2','813255-008','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813255,'A','TYPE2','813255-009','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813255,'A','TYPE2','813255-010','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813255,'A','TYPE2','813255-011','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813255,'A','TYPE2','813255-012','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813255,'A','TYPE2','89931','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813255,'A','TYPE2','91699','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813255,'A','TYPE2','J00390','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813255,'A','TYPE2','J00524','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813255,'A','TYPE2','J00533','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813255,'A','TYPE2','J00619','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813255,'A','TYPE2','J00755','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813255,'A','TYPE2','J00762','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813255,'A','TYPE2','J00779','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813255,'A','TYPE2','J02360','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813255,'A','TYPE2','J04155','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813016,'A','TYPE2','813016-004','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813016,'A','TYPE2','813016-005','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813016,'A','TYPE2','813016-006','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813016,'A','TYPE2','813016-007','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE2','510H0024','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE2','520H0022','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE2','520H0024','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE2','520H0030','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE2','520H0032','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE2','520H0035','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE2','530H0002','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE2','530H0016','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE2','530H0037','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE2','530H0047','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE2','698300P0070','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE2','780-1812','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE2','813012-001-01','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE2','813012-001-02','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE2','813022','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE2','91699','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE2','CG-1D','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE2','CG-1G','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE2','CG-1J','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE2','CG-1P','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',813012,'A','TYPE2','J01849','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',701209,'02','TYPE1','701336','C');
INSERT INTO TREESTRUCT VALUES ('TYPE1',701209,'02','TYPE2','2028511','1');
INSERT INTO TREESTRUCT VALUES ('TYPE1',701209,'02','TYPE2','334A5180','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',701209,'02','TYPE2','334A5537','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',701209,'02','TYPE2','92316','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',701209,'02','TYPE2','92318','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',701209,'02','TYPE2','CG-1Q','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',701209,'02','TYPE2','J00201','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',701209,'02','TYPE2','J00395','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',701209,'02','TYPE2','J00397','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',701209,'02','TYPE2','J00407','A');
INSERT INTO TREESTRUCT VALUES ('TYPE1',701209,'02','TYPE2','J00532','0');
go 
       
       
select * from    TREESTRUCT
go
10

But it's not enough to properly test with. Some replaces will do the whole lot. I just don't have the time to fix everyone's test code every time I try to help someone with a problem.

1 Like
11

no offense to you Jeff

please

i am having a lot of free time
:slight_smile:

12

Understood and I appreciate the reply. I'm just trying to make it clear to folks that they'll get better help more quickly if they post readily consumable data for someone to work the problem with and the code to do so actually works. It's the same as when they're trying to develop something. It's always a whole lot easier when they have access to viable test data and will be the first to complain if they don't. :wink:

1 Like