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)
 Can anybody help me translate this Oracle sql?
 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
22858 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  
 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