Showing results for 
Search instead for 
Did you mean: 

User-Defined Aggregate Functions

Former Member
0 Kudos

In Oracle it is possible to create User-define aggregate functions.

The MS SQL has the ability to create User-define aggregate functions in the CLR.

Foresees in the future this functionality in the ASE?

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

would that be useful :

Adaptive Server 15.0.2 introduces user-defined SQL functions.

Use create function to create user-defined functions, and drop function to remove a user-defined function.

You can include these elements in a scalar function:

  • declare statements to define data variables and cursors that are local to the function.
  • Assigned values to objects local to the function (for example, assigning values to scalar and variables local to a table with select or set commands).
  • Cursor operations that reference local cursors that are declared, opened, closed, and deallocated in the function.
  • Control-of-flow statements.
  • set options (only valid in the scope of the function).

Adaptive Server does not allow fetch statements in a scalar function that return data to the client. You cannot include :

  • select or fetch statements that returns data to the client.
  • insert, update, or delete statements.
  • Utility commands, such as dbcc, dump and load commands.
  • print statements
  • Statement that references rand, rand2, getdate, or newid.

You can include select or fetch statements that assign values only to local variable.

Former Member
0 Kudos

About this I know!

I'm interested in the aggregate functions.

Former Member
0 Kudos

can you please give an example on what are you trying to do ?

Former Member
0 Kudos

Very often, I have to analyze the time intervals.

The time intervals are stored as a decimal data type.

The number of 1.55 = 1 hour 55 minutes.

If you are using standard function sum(), these data are summed up correctly (as a decimal).

That is, 1.55 + 0.10 = 1.65, and in decimal notation it is right, but I need the result of 2.05 (2 hours 05 minutes).

Another example is an aggregate function list () (as in the IQ or SqlAnywhere).

LIST aggregate function | SCN

These tasks are, of course realizable! However, this requires extra time and effort to implement.

0 Kudos

Are you actually looking for time-series analysis functions?

Former Member
0 Kudos

Denis - I'm with you on this.

I work in the finance sector and user defined aggregate functions would be really useful for us.