Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
 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.06 seconds. Powered By: Snitz Forums 2000