0

It seems that Sybase omitted the function median() in Sybase ASE (15.x) while the typical, sum(), min(), max(), count(), etc... are available.

As a result, I was thinking that I could create a UDF (User Defined Function) that would fill that gap. I see a few examples of UDF taking a value (or fixed set of values) and returning a value; like this one: http://www.sypron.nl/udf.html.

Unfortunately, I don't see any example where a function takes an array of values as parameters. I saw the ugly hack to concatenate all the values into a long string and pass that, but I would rather try to explore a cleaner way of doing it. I could also require that whatever calls the function to insert the data into a predetermined tmp table that can then be read by the function, but that seems ugly too.

Any suggestions?

Charles
  • 50,943
  • 13
  • 104
  • 142
Jerome Provensal
  • 931
  • 11
  • 22

1 Answers1

1

You can use temporary table to use it as array. Consider below example

create table #t
(
  id int
)

insert into #T values (1)    

create function fun
returns int
as
   declare @id int
   select @id = id from #T
   return @id
go

select dbo.fun()
Robert
  • 25,425
  • 8
  • 67
  • 81
  • Thanks Parado, but I was looking for something that allows me to do it more conveniently so that I could use it like this. select median(col1), median(col2), median(col3) from MyTable where some = condition – Jerome Provensal Dec 17 '13 at 15:35
  • @jeromeso You should know that sybase hasn't arrays, so probably my code is the only one solution. – Robert Dec 17 '13 at 15:38
  • Thanks @parado, you are right. Have you had any experience with Java-based (vs. SQL-based) UDF? – Jerome Provensal Dec 17 '13 at 19:46