Dear all

Following is the code I have written (in FPD25) to generate a SQL Cursor for a given UniqueID. The selection process is working well but the hierarchy is not maintained as it should.

Please help.

Top
+- L1 A
   +- L2 A-1
      +- L3 A-1 A
   +- L2 A-2
+- L1 B
   +- L2 B-1

to be
Top
L1 A
L2 A-1
L3 A-1 A
L2 A-2
L1 B
L2 B-1

my code follows (please ignore my udfs):

*!*****************************************************************************
*!
*!       Function: SQLPARITEM()
*!
*!    Description:
*!
*!     Parameters:
*!
*!        Returns:
*!
*!*****************************************************************************
FUNCTION sqlparitem
	
	
	*** History
	***
	*** 12/07/02 Created


	PARAMETERS m.zuid


	PRIVATE ALL LIKE z*
	
	
	= closeused("itemmain")
	= closeused("itemheir")
	
	
	IF TYPE("m.zuid") = "L" AND m.zuid
		RETURN
	ENDIF
	
	
*-*	m.zmitem = open("a09mitem", "NOTAG")
	
	
	m.zctr   = 0
	m.zlevel = "1" + SPACE(49)
	
	
	SELECT m.zuid AS mainid, mitem.uniqueid, mitem.paritemid, m.zlevel AS level, mitem.desc, mitem.partno, mitem.subhead, mitem.opstock ;
		FROM a09mitem mitem ;
		WHERE mitem.uniqueid = m.zuid ;
		INTO CURSOR itemheir
brow norm
		
	
	SELECT * ;
		FROM itemheir ;
		INTO TABLE temp2
	
	
	DO WHILE .T.
		m.zctr   = m.zctr + 1
		m.zlevel = PADR(ALLTRIM(m.zlevel) + TRANSFORM(m.zctr, "@L 999"), 49)
		
		
		SELECT m.zuid AS mainid, mitem.uniqueid, mitem.paritemid, m.zlevel AS level, mitem.desc, mitem.partno, mitem.subhead, mitem.opstock ;
			FROM a09mitem mitem ;
			WHERE mitem.paritemid IN (SELECT uniqueid FROM temp2) ;
			INTO CURSOR temp
		
		
		= deletefile("temp2")
		
	
		SELECT * ;
			FROM temp ;
			INTO TABLE temp2
		
		
		IF _TALLY = 0
			EXIT
		ENDIF
		
		
		SELECT * ;
			FROM itemheir ;
		UNION ALL ;
		SELECT m.zuid AS mainid, mitem.uniqueid, mitem.paritemid, m.zlevel AS level, mitem.desc, mitem.partno, mitem.subhead, mitem.opstock ;
			FROM a09mitem mitem ;
			WHERE mitem.uniqueid IN (SELECT uniqueid FROM temp) ;
			INTO CURSOR itemheir
brow norm
	ENDDO
	
	
		
	SELECT m.zuid AS mainid, ustok.itemid AS uniqueid, ritem.paritemid, ritem.level, ritem.desc, ritem.partno, ritem.subhead, 0000000.0000 AS opstock, SUM(ustok.add) AS addbef, SUM(ustok.less) AS lessbef ;
		FROM a09ustok ustok, itemheir ritem ;
		WHERE ustok.itemid = ritem.uniqueid AND ;
			ustok.stockdate < m.fromdate ;
		GROUP BY 2 ;
	UNION ALL ;
	SELECT m.zuid AS mainid, ritem.uniqueid, ritem.paritemid, ritem.level, ritem.desc, ritem.partno, ritem.subhead, ritem.opstock, 00000000.000 AS addbef, 00000000.000 AS lessbef ;
		FROM itemheir ritem ;
		GROUP BY 2 ;
		INTO CURSOR temp

	SELECT m.zuid AS mainid, temp.uniqueid, temp.paritemid, temp.level, mitem.desc, mitem.partno, mitem.subhead, SUM(temp.opstock) AS opstock, SUM(temp.addbef) AS addbef, SUM(temp.lessbef) AS lessbef ;
		FROM temp, a09mitem mitem ;
		WHERE temp.mainid = mitem.uniqueid ;
		GROUP BY 1 ;
		INTO CURSOR itemmain
brow norm
		
		
	SELECT m.zuid AS mainid, temp.uniqueid, temp.paritemid, temp.level, temp.desc, temp.partno, temp.subhead, SUM(temp.opstock) AS opstock, SUM(temp.addbef) AS addbef, SUM(temp.lessbef) AS lessbef ;
		FROM temp ;
		GROUP BY 2 ;
		INTO CURSOR itemheir
		
		
	= closeused("temp")
	= deletefile("temp2")
	= closeused("temp3")
	
*-*	= close(m.zmitem, .T.)
	
	
	SELECT itemheir


	RETURN itemmain.opstock + itemmain.addbef - itemmain.lessbef