By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,609 Members | 2,246 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,609 IT Pros & Developers. It's quick & easy.

how to replicate the SQL Server 2005 Procedure to DB2?

P: 1
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
Share this question for a faster answer!
Share on Google+

Post your reply

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