Custom User Aggregate Fuction - SQLite

[m227]

Hi, you are boasting that "Custom SQL aggregate functions are supported" since UniDAC 7.1
I have devoted one hour and did not find anywhere how to use this facility.
Could you enlighten me?
The only trace I found was for LiteDAC:
viewtopic.php?f=48&t=38101&p=132659&hil ... ns#p132659

[MaximG]

The following code sample demonstrates how to register an aggregate function <your function name> with 1 argument:

Code: Select all

  ...
  UniConnection.Connect;
  TLiteUtils.RegisterAggregateFunction(UniConnection, '<your function name>', 1, YourStepProcedure, YourFinalFunction);
  ...

  ...
  procedure YourStepProcedure(InValues: array of Variant);
  begin
    ...
  end;
  ...
  function YourFinalFunction: Variant;
  begin
    ...
  end;

We'll add the description to the product documentation.

[m227]

Hi, thank you for a response. I managed to create MEDIAN function and it works. The only problem is that if I want to use such function twice in a query it fails, as when passing parameters to <YourStepProcedure> i don't know from which column they come. My sample SQLite query.

Code: Select all

CREATE TEMPORARY TABLE TestData1 (a INT);
CREATE TEMPORARY TABLE TestData2 (a INT);
INSERT INTO TestData1 VALUES (7),(14),(21),(3),(9),(2),(4);     -- Median = 7
INSERT INTO TestData2 VALUES (7),(14),(21),(3),(9),(2),(4),(5); -- Median = 6
SELECT Median2(TestData1.A), Median2(TestData2.A)
  FROM TestData1, TestData2; 

Shall I use any MOD to differentiate myself from which column data came?

[Stellar]

When invoking a handler of a user defined function, SQLite doesn't pass any information about the function context.
You can try solving the issue in two ways:
- add two functions with identical logic
- add one more parameter to the function and pass a unique argument for each of the functions. For example:

Code: Select all

SELECT Median2(1, TestData1.A), Median2(2, TestData2.A) FROM TestData1, TestData2;