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)
 Can anybody help me translate this Oracle sql?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pednoy
Starting Member

2 Posts

Posted - 07/22/2004 :  02:50:19  Show Profile  Reply with Quote
Can anybody help me translate this SQL of Oracle to SQL server?
IT will be a grateful.



PROCEDURE max_user IS
lmax PLS_INTEGER; lday PLS_INTEGER;
BEGIN
SELECT COUNT(*) INTO lmax FROM sapr3.USR41 WHERE mandt = '240';
SELECT COUNT(*) INTO lday FROM sapr3.ZBC_MAX_USER WHERE sample_date = TRUNC(SYSDATE);
IF lday > 0 THEN
UPDATE sapr3.ZBC_MAX_USER SET MAX = lmax WHERE sample_date = TRUNC(SYSDATE) AND MAX < lmax;
COMMIT WORK;
ELSE
INSERT INTO sapr3.ZBC_MAX_USER (sample_date, MAX) VALUES (TRUNC(SYSDATE), lmax);
COMMIT WORK;
ELSE
INSERT INTO sapr3.ZBC_MAX_USER (sample_date, MAX) VALUES (TRUNC(SYSDATE), lmax);
COMMIT WORK;
END IF;
END max_user;

Kristen
Test

United Kingdom
22431 Posts

Posted - 07/22/2004 :  03:34:57  Show Profile  Reply with Quote
No idea if this is close, but it might be a starter-for-ten

Kristen

CREATE PROCEDURE dbo.max_user
	@lmax int OUTPUT,	-- I don't know what PLS_INTEGER is, I've assumed an "int"
	@lday int OUTPUT	-- PLS_INTEGER
AS

DECLARE	@TRUNC_SYSDATE	datetime

-- I have assumed that TRUNC(SYSDATE) is Today's date (with NO time)
SELECT	@TRUNC_SYSDATE = DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate()))

SELECT	@lmax = COUNT(*) 
FROM	sapr3.USR41 
WHERE	mandt = '240'

SELECT	@lday = COUNT(*)
FROM	sapr3.ZBC_MAX_USER 
WHERE	    sample_date >= @TRUNC_SYSDATE
	AND sample_date < DATEADD(Day, 1, @TRUNC_SYSDATE)	-- Before tomorrow

IF @lday > 0 THEN
BEGIN
	UPDATE	sapr3.ZBC_MAX_USER 
	SET	[MAX] = @lmax 
	WHERE	    sample_date = @TRUNC_SYSDATE
		AND [MAX] < @lmax
--	COMMIT WORK
END
ELSE
BEGIN
	INSERT INTO sapr3.ZBC_MAX_USER (sample_date, [MAX]) VALUES (@TRUNC_SYSDATE, @lmax);
--	COMMIT WORK;
END
-- This duplciate ELSE presumably doesn;t do anything in Oracle??
-- ELSE
-- BEGIN
-- 	INSERT INTO sapr3.ZBC_MAX_USER (sample_date, [MAX]) VALUES (@TRUNC_SYSDATE, @lmax);
-- --	COMMIT WORK;
-- END
GO
Go to Top of Page

pednoy
Starting Member

2 Posts

Posted - 07/22/2004 :  03:45:47  Show Profile  Reply with Quote
Thank you very much. I will try it.
You are so nice.

Go to Top of Page
  Previous Topic Topic Next Topic  
 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.06 seconds. Powered By: Snitz Forums 2000