Oracle to MS SQL Server 2008 Code Conversion Problems [closed]

Question

      CREATE OR REPLACE FUNCTION CHI_X2 (a1 in number, b1 in number, a2 in number, b2 in number) 

       RETURN NUMBER IS
       DECLARE @tr1 INT;
       DECLARE @tr2 INT;
       DECLARE @tc1 INT;
       DECLARE @tc2 INT;
       DECLARE @ca1 INT;
       DECLARE @ca2 INT;
       DECLARE @cb1 INT;
       DECLARE @cb2 INT;
       DECLARE @xi INT;
       DECLARE @nt INT;

       CREATE PROCEDURE ()
       AS
       BEGIN

         SET tr1 = a1+b1
         SET tr2 = a2+b2
         SET tc1 = a1+a2
         SET tc2 = b1+b2
         SET nt = tr1+tr2
         SET ca1 =(tc1/nt*tr1)
         SET ca2 =(tc1/nt*tr2)
         SET cb1 =(tc2/nt*tr1)
         SET cb2 =(tc2/nt*tr2)
         SET xi =((power((a1 -ca1),2)/ca1)+(power((a2 -ca2),2)/ca2)+(power((b1 -cb1),2)/cb1)+(power((b2-cb2),2)/cb2))
        return xi
        END CHI_X2

       CREATE PROCEDURE ()
       AS
       begin

        DECLARE @max_chi INT
 DECLARE @xi INT
 DECLARE @maxpos INT
 DECLARE @n INT
 DECLARE @SWV_CUR_OUT_sno VARCHAR(255)
 DECLARE @SWV_CUR_OUT_p VARCHAR(255)
 DECLARE @SWV_CUR_OUT_t VARCHAR(255)
 DECLARE @SWV_cursor_var1 CURSOR
 DECLARE @SWV_CUR_IN_sno VARCHAR(255)
 DECLARE @SWV_CUR_IN_p VARCHAR(255)
 DECLARE @SWV_CUR_IN_t VARCHAR(255)
 delete from CH_TABLE
 commit
 SET @SWV_cursor_var1 = CURSOR  FOR select sessionnumber, sessioncount, timespent from CH_TABLE  

       order by sessionnumber asc
 OPEN @SWV_cursor_var1
 FETCH NEXT FROM @SWV_cursor_var1 INTO 

       @SWV_CUR_OUT_sessionnumber,@SWV_CUR_OUT_sessioncount,@SWV_CUR_OUT_timespent
 while @@FETCH_STATUS = 0
 begin
        SET @max_chi = -999
        SET @maxpos = NULL
        SET @SWV_cursor_var1 = CURSOR  FOR select sessionnumber, sessioncount, timespent from      CH_TABLE  

       order by sessionnumber asc
        OPEN @SWV_cursor_var1
        FETCH NEXT FROM @SWV_cursor_var1 INTO 

       @SWV_CUR_IN_sessionnumber,@SWV_CUR_IN_sessioncount,@SWV_CUR_IN_timespent
        while @@FETCH_STATUS = 0
        begin
           select   @n = count(*) from(select x1 as x from CH_TABLE union all select x2 from      CH_TABLE) AS TabAl
 where x = @SWV_CUR_OUT_sessionnumber or x = @SWV_CUR_IN_sessionnumber
        if n = 0
       begin
          SET xi =                round(CHI_X2(cur_out.sessioncount,cur_out.timespent,cur_in.sessioncount,cur_in.timespent),2)
           if xi > max_chi
           begin
        SET max_chi = xi
        SET maxpos = cur_in.sessionnumber
          end
       end

           FETCH NEXT FROM @SWV_cursor_var1 INTO 

       @SWV_CUR_IN_sessionnumber,@SWV_CUR_IN_sessioncount,@SWV_CUR_IN_timespent
        end
       if max_chi > -999
       begin
       INSERT INTO CH_TABLE(X1, X2, VALUE)
       VALUES(cur_out.sessionnumber, maxpos, max_chi)

           commit
   end


    CLOSE @SWV_cursor_var1
    FETCH NEXT FROM @SWV_cursor_var1 INTO 

       @SWV_CUR_OUT_sessionnumber,@SWV_CUR_OUT_sessioncount,@SWV_CUR_OUT_timespent
 end
 CLOSE @SWV_cursor_var1
   END

Hye, there everyone, I'm new to everything here just need to that I have converted the following code into MS SQL SERVER 2008 from Oracle! Now it has some errors though I have done it and NEW to SQL SERVER 2008 Please correct my code;it has some errors! I don't know about how to get rid as I'm new! Please can somebody help me It will be a great effort like a teacher! I will be thankful! Thanks in advance...

Following Expected errors:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'OR'.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near '='.
Msg 178, Level 15, State 1, Line 27
A RETURN statement with a return value cannot be used in this context.
Msg 102, Level 15, State 1, Line 28
Incorrect syntax near 'CHI_X2'.
Msg 134, Level 15, State 1, Line 36
The variable name '@xi' has already been declared. Variable names must be unique within a query batch or stored procedure.
Msg 137, Level 15, State 2, Line 49
Must declare the scalar variable "@SWV_CUR_OUT_sessionnumber".
Msg 137, Level 15, State 2, Line 56
Must declare the scalar variable "@SWV_CUR_IN_sessionnumber".
Msg 137, Level 15, State 2, Line 60
Must declare the scalar variable "@SWV_CUR_OUT_sessionnumber".
Msg 102, Level 15, State 1, Line 63
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Line 66
Incorrect syntax near '='.
Msg 137, Level 15, State 2, Line 71
Must declare the scalar variable "@SWV_CUR_IN_sessionnumber".
Msg 137, Level 15, State 2, Line 83
Must declare the scalar variable "@SWV_CUR_OUT_sessionnumber".
Msg 102, Level 15, State 1, Line 86
Incorrect syntax near 'END'.

if I were you I would do some google searching onthe known errors that you have. like how to create a SQL Server function first. maybe some SQL Server Syntax as well
I have been trying to plug in random numbers and I keep getting a Divide by Zero ERROR....lol
the question is tagged with the Oracle Tag and the SQL Server tag. and the code probably did work in Oracle at some point. Granted the user could have done more to bring the question closer to the right syntax, I kind of lean towards this being a review topic, but I am right on the line because the code had so many errors, it would almost be a good question for StackOverflow, but I fear that it would have received more down votes there than it did here. I agree that the code didn't even look like SQL Server.

Malachi · Answer 1 · 2013-08-22 18:47:18Z

first you should start with the basic structure of a Scalar Function

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName> 
(
    -- Add the parameters for the function here
    <@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS <Function_Data_Type, ,int>
AS
BEGIN
-- Declare the return variable here
DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>

-- Add the T-SQL statements to compute the return value here
SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>

-- Return the result of the function
RETURN <@ResultVar, sysname, @Result>

END
GO

this is just the Template that SSMS gave me when I went to create a New Scalar Function.

Solution

I strongly suggest you do some research into using SQL Server Syntax a lot more

Here is what I came up with but I haven't tested it either.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Name
-- Create date: 
-- Description: 
-- =============================================
CREATE FUNCTION FunctionName 
(
-- Add the parameters for the function here
@a1 INT,
@b1 INT,
@a2 INT,
@b2 INT
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @Result int

DECLARE @tr1 INT;
   DECLARE @tr2 INT;
   DECLARE @tc1 INT;
   DECLARE @tc2 INT;
   DECLARE @ca1 INT;
   DECLARE @ca2 INT;
   DECLARE @cb1 INT;
   DECLARE @cb2 INT;
   DECLARE @xi INT;
   DECLARE @nt INT;

SET @tr1 = @a1+@b1
SET @tr2 = @a2+@b2
SET @tc1 = @a1+@a2
SET @tc2 = @b1+@b2
SET @nt = @tr1+@tr2
SET @ca1 =(@tc1/@nt*@tr1)
SET @ca2 =(@tc1/@nt*@tr2)
SET @cb1 =(@tc2/@nt*@tr1)
SET @cb2 =(@tc2/@nt*@tr2)
SET @xi =((power((@a1 -@ca1),2)/@ca1)+(power((@a2 -@ca2),2)/@ca2)+(power((@b1 -@cb1),2)/@cb1)+(power((@b2-@cb2),2)/@cb2))

-- Add the T-SQL statements to compute the return value here
SELECT @Result = @xi

-- Return the result of the function
RETURN @Result

END
GO

all of your Variables need to have @ at the beginning of them.

and from the example you can sort of see the syntax for declaring the variables.

asked	1 month ago
viewed	37 times
active	1 month ago

Oracle to MS SQL Server 2008 Code Conversion Problems [closed]

closed as off-topic by Jamal, Jeff Vanzella, Lstor, Brian Reichle, Aseem Bansal Aug 23 at 6:35

1 Answer

Not the answer you're looking for? Browse other questions tagged sql sql-server oracle or ask your own question.

Oracle to MS SQL Server 2008 Code Conversion Problems [closed]

closed as off-topic by Jamal, Jeff Vanzella, Lstor, Brian Reichle, Aseem Bansal Aug 23 at 6:35

1 Answer

Not the answer you're looking for? Browse other questions tagged sql sql-server oracle or ask your own question.

Related