help help help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

help help help

Author

Topic

mdanwerali
Starting Member

30 Posts

Posted - 2002-11-08 : 01:35:00

Hi,

Can u please tell how to write the following function in Sql server.

"select greatest(1,2) from dual" This following code in Oracle returns the greatest of the two numbers and the out result is 2.

So the same function can be used with Dates...

What is the Equivalent function or code in the Sql Server.

Thanks in Advance

Md Anwer Ali.

mohamedyousuff@yahoo.com
Starting Member

24 Posts

Posted - 2002-11-08 : 01:51:44

You need to write a User-Defined function for this.

CREATE FUNCTION greatest (@num1 int, @num2 int)
RETURNS int AS
BEGIN
if ( @num1 > @num2 )
return @num1
return @num2
END

Then use the query
select dbo.greatest(1,2)

quote:

Hi,

Can u please tell how to write the following function in Sql server.

"select greatest(1,2) from dual" This following code in Oracle returns the greatest of the two numbers and the out result is 2.

So the same function can be used with Dates...

What is the Equivalent function or code in the Sql Server.

Thanks in Advance

Md Anwer Ali.

mdanwerali
Starting Member

30 Posts

Posted - 2002-11-08 : 02:06:06

Thanks for the information but this will work only with two parameters.... how if the values are more than two or the parameters are more than two

quote:

You need to write a User-Defined function for this.

CREATE FUNCTION greatest (@num1 int, @num2 int)
RETURNS int AS
BEGIN
if ( @num1 > @num2 )
return @num1
return @num2
END

Then use the query
select dbo.greatest(1,2)

quote:

Hi,

Can u please tell how to write the following function in Sql server.

"select greatest(1,2) from dual" This following code in Oracle returns the greatest of the two numbers and the out result is 2.

So the same function can be used with Dates...

What is the Equivalent function or code in the Sql Server.

Thanks in Advance

Md Anwer Ali.

mohamedyousuff@yahoo.com
Starting Member

24 Posts

Posted - 2002-11-08 : 03:43:19

I dont think one can pass a variable number of arguments to a function. In such situations one can insert those arguments in another table and call this function with a single parameter indicating the collection of arguments in the arguments-table. Appropriate logic has to be written in the function to find the greatest value.

Another possibility is to pass the parameters as a comma separated list of values and evaluate it in the function.

quote:

Thanks for the information but this will work only with two parameters.... how if the values are more than two or the parameters are more than two

quote:

You need to write a User-Defined function for this.

CREATE FUNCTION greatest (@num1 int, @num2 int)
RETURNS int AS
BEGIN
if ( @num1 > @num2 )
return @num1
return @num2
END

Then use the query
select dbo.greatest(1,2)

quote:

Hi,

Can u please tell how to write the following function in Sql server.

"select greatest(1,2) from dual" This following code in Oracle returns the greatest of the two numbers and the out result is 2.

So the same function can be used with Dates...

What is the Equivalent function or code in the Sql Server.

Thanks in Advance

Md Anwer Ali.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-11-08 : 06:51:19

following article may help towards part of the solution....especially with regard to "In such situations one can insert those arguments in another table"

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=6206

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-08 : 06:54:11

This is a perfect example of how Oracle is not set based. In a true relational databaase, your values (1,2,....n) would be handled as a set, and you would apply the max aggregate against that set.


select max(a.i)
from (select 1 as i union select 2) a

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources