467,882 Members | 1,241 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,882 developers. It's quick & easy.

how to replicate the SQL Server 2005 Procedure to DB2?

ALTER PROCEDURE TEST_UPF


AS

DECLARE @LOGON VARCHAR(32);

DECLARE @LEVEL INT;
DECLARE @LOGON_COUNT INT;

DECLARE LOGON_CUR cursor for
SELECT LOGON FROM dbo.USER_PRODUCT_FACT_RPL
WHERE LEVEL = 4;


DECLARE LOGON_CNT_CUR cursor for
SELECT COUNT(*) LOGON FROM dbo.USER_PRODUCT_FACT_RPL
WHERE LEVEL = 4;


OPEN LOGON_CUR ;
OPEN LOGON_CNT_CUR ;

FETCH NEXT FROM LOGON_CNT_CUR INTO @LOGON_COUNT

WHILE(@LOGON_COUNT <> 0)
BEGIN

FETCH NEXT FROM LOGON_CUR INTO @LOGON

INSERT PROCE_LOGONS_OUTPUT_NARSIMHA_TEST
SELECT DISTINCT W.PARENT,W.LOGON,W.CUSTOMER_NUMBER,W.LEVEL, 0
FROM dbo.USER_PRODUCT_FACT_RPL W
WHERE W.[LOGON] = @LOGON

SELECT @LEVEL= LEVEL FROM PROCE_LOGONS_OUTPUT_NARSIMHA_TEST WHERE LOGON = @LOGON

WHILE @LEVEL > 1
BEGIN

INSERT INTO PROCE_LOGONS_OUTPUT_NARSIMHA_TEST
SELECT DISTINCT w.PARENT,c.LOGON,c.CUSTOMER_NUMBER,W.LEVEL,@LEVEL-1
FROM dbo.USER_PRODUCT_FACT_RPL W, PROCE_LOGONS_OUTPUT_NARSIMHA_TEST C
WHERE C.PARENT = W.LOGON AND W.LEVEL = @LEVEL-1
AND C.LOGON=@LOGON AND W.PARENT IS NOT NULL



SET @LEVEL = @LEVEL - 1
END

SET @LOGON_COUNT = @LOGON_COUNT - 1

END
CLOSE LOGON_CUR;
DEALLOCATE LOGON_CUR;

CLOSE LOGON_CNT_CUR;
DEALLOCATE LOGON_CNT_CUR;

SELECT * FROM PROCE_LOGONS_OUTPUT_NARSIMHA_TEST
order by logon,level

GO
Aug 14 '07 #1
  • viewed: 849
Share:

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Chris Auer | last post: by
17 posts views Thread by Danieltbt05 | last post: by
1 post views Thread by chloe.crowder | last post: by
reply views Thread by MrMoon | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.