Post reply

Oracle Migration

  • March 25, 2004 at 12:42 am

    #162228

    For Oracle function GREATEST , We don't have equivalent support in SQL Server,

    For Example :

    SELECT GREATEST ('HARRY', 'HARRIOT', 'HAROLD')

       "Greatest" FROM DUAL;

    The output is  :

    Greatest

    --------

    HARRY

    How can we bring the same functiontnality in SQL Server

     

  • March 25, 2004 at 1:28 am

    #500376

    Unfortunately there is no function which supports the same functionality in SQL Server.

    Thanks

    Prasad Bhogadi
    www.inforaise.com

  • March 25, 2004 at 4:39 am

    #500396

    Don't have time right now to validate it but if you know the limit of how many inputs you will need add 10 and create a UDF (user defined function) something like this (note requires SQL 2000)

     

    CREATE FUNCTION dbo.Greatest (@var1 varchar(30) = NULL,

    @var2 varchar(30) = NULL,

    @var3 varchar(30) = NULL,

    @var4 varchar(30) = NULL,

    @var5 varchar(30) = NULL,

    @var6 varchar(30) = NULL,

    @var7 varchar(30) = NULL,

    @var8 varchar(30) = NULL,

    @var9 varchar(30) = NULL,

    @var10 varchar(30) = NULL)

    RETURNS VARCHAR(30)

    AS

    BEGIN

     DECLARE @tbl TABLE (var varchar(30))

     INSERT @tbl VALUES (@var1)

     INSERT @tbl VALUES (@var2)

     INSERT @tbl VALUES (@var3)

     INSERT @tbl VALUES (@var4)

     INSERT @tbl VALUES (@var5)

     INSERT @tbl VALUES (@var6)

     INSERT @tbl VALUES (@var7)

     INSERT @tbl VALUES (@var8)

     INSERT @tbl VALUES (@var9)

     INSERT @tbl VALUES (@var10)

     RETURN (SELECT MAX(var) FROM @tbl WHERE var IS NOT NULL)

    END

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply