Help with query to "loop"? through records.

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)

Help with query to "loop"? through records.

Author

Topic

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2006-10-03 : 18:53:26

What I am trying to do:
Given an item number "PINBR", select all the records "CINBR" which have an 'ITTYP' 9, and continue
on down the tree to select it's children item numbers. This should be done by temporarily
re-assigning the "PINBR" as the "CINBR" if the 'ITTYP' is equal to '9'. Note: Not all 'ITTYP' 9 will have children.

For example, if I used this statement:

SELECT CINBR FROM #temp1 WHERE PINBR = '092155' AND ITTYP = '9'

I would like to end up with these results

CINBR
-----
012345
023854
046893
069799
078324


CREATE TABLE #temp1 (
	[PINBR] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CINBR] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ITTYP] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[QTYPR] [decimal](11, 3) NOT NULL ,
) ON [PRIMARY]
INSERT #temp1 ([PINBR], [CINBR], [ITTYP], [QTYPR]) 
 VALUES ('092155', '041356', '4', '1')
INSERT #temp1 ([PINBR], [CINBR], [ITTYP], [QTYPR]) 
 VALUES ('092155', '012345', '9', '1')
INSERT #temp1 ([PINBR], [CINBR], [ITTYP], [QTYPR]) 
 VALUES ('092155', '032314', '1', '1')
INSERT #temp1 ([PINBR], [CINBR], [ITTYP], [QTYPR]) 
 VALUES ('092155', '055321', '0', '1')
INSERT #temp1 ([PINBR], [CINBR], [ITTYP], [QTYPR]) 
 VALUES ('078324', '046738', '4', '1')
INSERT #temp1 ([PINBR], [CINBR], [ITTYP], [QTYPR]) 
 VALUES ('078324', '046983', '9', '1')
INSERT #temp1 ([PINBR], [CINBR], [ITTYP], [QTYPR]) 
 VALUES ('092155', '078324', '9', '1')
INSERT #temp1 ([PINBR], [CINBR], [ITTYP], [QTYPR]) 
 VALUES ('092166', '058328', '0', '1')
INSERT #temp1 ([PINBR], [CINBR], [ITTYP], [QTYPR]) 
 VALUES ('055321', '054523', '4', '1')
INSERT #temp1 ([PINBR], [CINBR], [ITTYP], [QTYPR]) 
 VALUES ('055321', '067483', '4', '3')
INSERT #temp1 ([PINBR], [CINBR], [ITTYP], [QTYPR]) 
 VALUES ('055321', '067343', '4', '2')
INSERT #temp1 ([PINBR], [CINBR], [ITTYP], [QTYPR]) 
 VALUES ('012345', '023854', '9', '1')
INSERT #temp1 ([PINBR], [CINBR], [ITTYP], [QTYPR]) 
 VALUES ('023854', '069783', '4', '2')
INSERT #temp1 ([PINBR], [CINBR], [ITTYP], [QTYPR]) 
 VALUES ('023854', '069799', '9', '1')

-- Select loop statment or function
GO
DROP TABLE #temp1

Hope I made sense.
Thanks!

Jose

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-03 : 22:31:37

this might help http://www.sqlteam.com/item.asp?ItemID=8866

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources