• Advertisement
Sign in to follow this  

[web] [SQL/Oracle] Need help porting a function from MSSQL to Oracle...

This topic is 3829 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

I am doing this project, but am having trouble with Oracle syntax. I created this function in MS SQL, but it doesn't match up at all with Oracle. Oracle only says "Warning: Function created with compilation errors." but never can run it. I have it working just fine in MS SQL 2005. Here is what I have... MS SQL Function (SQL Query)
CREATE FUNCTION CheckDriverExists
(
        @p_DriverNo varchar(5)
) RETURNS INT
AS
BEGIN
	DECLARE @TempColumn INT
	DECLARE @TempColumn2 INT

	SET @TempColumn = (SELECT COUNT(DISTINCT YDrivers.DriverNo) AS TempColumn 
	FROM YDrivers WHERE YDrivers.DriverNo=@p_DriverNo);

	SET @TempColumn2 = (SELECT COUNT(DISTINCT XDrivers.DriverNo) AS TempColumn 
	FROM XDrivers WHERE XDrivers.DriverNo=@p_DriverNo);

	RETURN (@TempColumn + @TempColumn2)
END;
Oracle Function
CREATE OR REPLACE FUNCTION CheckDriverExists(p_DriverNo varchar(5)) RETURNS INT
IS
BEGIN
     count1 := (SELECT COUNT(DISTINCT YDrivers.DriverNo) AS TempColumn
	FROM YDrivers WHERE YDrivers.DriverNo=p_DriverNo);

     count2 := (SELECT COUNT(DISTINCT XDrivers.DriverNo) AS TempColumn
	FROM XDrivers WHERE XDrivers.DriverNo=p_DriverNo);

     RETURN (count1 + count2);
END;
/

Share this post


Link to post
Share on other sites
Advertisement
Nevermind, found this awesome program to convert SQL statements...

http://www.swissql.com/products/sqlserver-to-oracle/tsql2plspdownloadform.html

CREATE OR REPLACE FUNCTION CheckDriverExists
(
p_DriverNo IN VARCHAR2 DEFAULT NULL
)
RETURN INT
AS
TempColumn INT;
TempColumn2 INT;
BEGIN
SELECT COUNT(DISTINCT YDrivers.DriverNo) AS TempColumn INTO TempColumn
FROM YDrivers
WHERE YDrivers.DriverNo = CheckDriverExists.p_DriverNo;
SELECT COUNT(DISTINCT XDrivers.DriverNo) AS TempColumn INTO TempColumn2
FROM XDrivers
WHERE XDrivers.DriverNo = CheckDriverExists.p_DriverNo;
RETURN ( TempColumn + TempColumn2 );
END;
/

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement