SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Displaying a Tree in Order
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

X002548
Not Just a Number

15586 Posts

Posted - 11/14/2006 :  12:23:24  Show Profile  Reply with Quote
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



Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/14/2006 :  14:15:45  Show Profile  Reply with Quote
OK, the script has been cleaned up

Should run with no problem now



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



Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000