locked
Need help to recreate 2 Oracle functions in SQL Server RRS feed

  • Question

  • Can someone please help me rewrite these two Oracle functions for SQL Server?  I am afraid I do not know enough SQL specific to either server to do it and SSMA is complaining that it can't either.

    CREATE OR REPLACE FUNCTION intdiv( a NUMBER, b NUMBER) RETURN NUMBER IS OUT NUMBER;
      BEGIN
         IF (a IS NULL) OR (b IS NULL) THEN
            RETURN NULL;
         ELSE
            out := trunc( round(a) / round(b) );
            RETURN out;
         END IF;
      END;

     

    SSMA doesn't like two sections of this one:

     * SSMA error messages:

    * O2SS0004: Unparsed SQL [OUT NUMBER;] cannot be converted.

    * O2SS0004: Unparsed SQL [out := trunc( round(a) / round(b) );

    * RETURN out;] cannot be converted

    The other Oracle function looks like this:

    CREATE OR REPLACE FUNCTION isn( a IN NUMBER, b IN VARCHAR2 ) RETURN NUMBER AS out NUMBER;
      BEGIN
         IF ( LOWER(b) = LOWER('NULL') ) THEN
            IF ( a IS NULL ) THEN
               RETURN -1;
            ELSE
               RETURN 0;
            END IF;
         ELSE
            IF ( a IS NOT NULL ) THEN
               RETURN -1;
            ELSE
               RETURN 0;
            END IF;
         END IF;
      END;

    SSMA has one of the same problems with this one:

     *   SSMA error messages:

     *   O2SS0004: Unparsed SQL [out NUMBER;] cannot be converted.

    Monday, August 13, 2012 9:12 PM

Answers

  • Also, for what it is worth, look at is_really_numeric and is_really_integer at:

    http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

    RLF

    • Marked as answer by MnM Show Friday, August 17, 2012 7:10 PM
    Tuesday, August 14, 2012 2:53 PM
  • hi,

    Here is the first one:

    CREATE FUNCTION dbo.intdiv(@a numeric = null, @b numeric = null) 
    RETURNS int
    AS
    BEGIN
      RETURN CAST((@a / NULLIF(@b,0)) AS int);
    END;
    GO

    I'd use the ISNUMERIC built-in function for the second one.

    I hope it helps.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.

    My Blog | Hire Me



    • Edited by Janos BerkeMVP Tuesday, August 14, 2012 12:59 PM div/0 excl. by nullif
    • Marked as answer by MnM Show Friday, August 17, 2012 7:10 PM
    Tuesday, August 14, 2012 12:56 PM

All replies

  • hi,

    Here is the first one:

    CREATE FUNCTION dbo.intdiv(@a numeric = null, @b numeric = null) 
    RETURNS int
    AS
    BEGIN
      RETURN CAST((@a / NULLIF(@b,0)) AS int);
    END;
    GO

    I'd use the ISNUMERIC built-in function for the second one.

    I hope it helps.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.

    My Blog | Hire Me



    • Edited by Janos BerkeMVP Tuesday, August 14, 2012 12:59 PM div/0 excl. by nullif
    • Marked as answer by MnM Show Friday, August 17, 2012 7:10 PM
    Tuesday, August 14, 2012 12:56 PM
  • Also, for what it is worth, look at is_really_numeric and is_really_integer at:

    http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

    RLF

    • Marked as answer by MnM Show Friday, August 17, 2012 7:10 PM
    Tuesday, August 14, 2012 2:53 PM
  • So does this look equivalent to the second one? 

    CREATE FUNCTION dbo.isn 
    (  
        @num VARCHAR(64)  
    )  
    RETURNS int  
    BEGIN  
        IF LEFT(@num, 1) = '-'  
            SET @num = SUBSTRING(@num, 2, LEN(@num))  
     
        DECLARE @pos TINYINT  
     
        SET @pos = 1 + LEN(@num) - CHARINDEX('.', REVERSE(@num))  
     
        RETURN CASE  
        WHEN PATINDEX('%[^0-9.-]%', @num) = 0  
            AND @num NOT IN ('.', '-', '+', '^') 
            AND LEN(@num)>0  
            AND @num NOT LIKE '%-%' 
            AND  
            (  
                ((@pos = LEN(@num)+1)  
                OR @pos = CHARINDEX('.', @num))  
            )  
        THEN  
            -1  
        ELSE  
        0  
        END  
    END  
    GO

    I modified it from the link you posted Russell.  Honestly I can't tell which condition is returning which value.  If something is calling this function I want it to return the same -1 or 0 like it is in Oracle so I don't have to fix that also.  I anticipate having to fix a lot of stuff after the migration but the less stuff the better.  So I changed it from RETURN BIT to RETURN int because I assume a bit will not allow a value of -1, but is it still returning -1 and 0 for the same reasons or did I get them backwards?

    Thanks Janos for the first one, I'll give you points when the thread is concluded.

    Tuesday, August 14, 2012 9:09 PM
  • It looks OK to me.  Is it correct to understand that for Oracle -1 = 'Yes'? 

    Your isn is the Is_Really_Numeric equivalent.  I ran some test cases with it and it looks correct to me.

    SELECT dbo.isn('23')    -- -1
    SELECT dbo.isn('-23')   -- -1
    SELECT dbo.isn('123.45')-- -1
    SELECT dbo.isn('a23')
    SELECT dbo.isn('23-')
    SELECT dbo.isn('x*')
    SELECT dbo.isn(NULL)
    SELECT dbo.isn('-1,123.45')
    SELECT dbo.isn('123.45-')

    As you see, the first 3 examples return -1 meaning that they are 'really numeric'.   The other strings do not pass the rule.

    RLF


    • Edited by SQLWork Wednesday, August 15, 2012 1:50 PM
    Wednesday, August 15, 2012 1:49 PM
  •  Is it correct to understand that for Oracle -1 = 'Yes'? 



    I don't think it is a standard but I think this function was written that way.  The reason I say that is that there are about 15 or so functions on the Oracle server and all of them except for these two SSMA seems happy to translate.  None of the other ones return -1 and 0 although some of them do return 1 and 0.  I'm taking good notes so I think if I got them backwards I will remember it if I see something calling thus function that seems to be misbehaving.  I will be doing my migration into a test SQL Server quite a few times before I try it for real anyway.  Thanks for your help, much appreciated.
    Friday, August 17, 2012 7:10 PM