Well here's a code snipetof the update for the move of an entire branch to another branch...I just need to finish up the rest of the script...now I now it probably could be consolidated...but I'm building in D2 limitations so I can hand it off as a spec to a developer who is going to build a DB2 EXTERNAL Stored Procedure in COBOL...man I hate DB2 lately
DECLARE @MNGR_CODEX varchar(800), @NEW_CODEX varchar(800), @CHNG_CODEX varchar(800)
SELECT @MNGR_CODEX = RTRIM(CODEX) FROM myPositions99 WHERE Employee = 'Pat'
SELECT @CHNG_CODEX = RTRIM(CODEX) FROM myPositions99 WHERE Employee = 'Nick'
SELECT @NEW_CODEX = @MNGR_CODEX+RIGHT(RTRIM('00000'+CONVERT(varchar(800),(MAX(NEW_CODEX)+1))),5)
FROM (SELECT CONVERT(int,SUBSTRING(CODEX,LEN(@MNGR_CODEX)+1,5)) AS NEW_CODEX
FROM myPositions99
WHERE CODEX LIKE @MNGR_CODEX+'%'
AND LEN(RTRIM(CODEX)) = LEN(@MNGR_CODEX)+5) AS XXX
SELECT @MNGR_CODEX, @NEW_CODEX, @CHNG_CODEX
UPDATE P
SET CODEX = @NEW_CODEX + SUBSTRING(CODEX,LEN(RTRIM(@CHNG_CODEX))+1,800-LEN(RTRIM(@CHNG_CODEX)))
FROM myPositions99 P
WHERE CODEX LIKE @CHNG_CODEX + '%'
Brett
8-)
Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Add yourself!
http://www.frappr.com/sqlteam
Displaying a Tree in Order
Posted - 11/14/2006 : 12:23:24
