I have a table with memo fields. What i need is the 'sum' of to MEMO fields
(concat) in a group by.

Is it possible to add a custom function that does this?

id    memo
1    text1
1    text2
1    text3
2    text1
2    text2

result:
1, text1text2text3
2, text1text2


Wierd Snieder

Wierd,

<< I have a table with memo fields. What i need is the 'sum' of to MEMO
fields (concat) in a group by.

Is it possible to add a custom function that does this? >>

Unfortunately, no.  DBISAM does not support aggregate functions with the
custom functions.   I can, however, add it as a future enhancement.
ElevateDB does it via the LIST() aggregate function.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Wierd Snieder" <wierd@yowitec.com> wrote in message
news:0617FBBC-F7E2-43FB-8AB2-A6829249ED25@news.elevatesoft.com...
>I have a table with memo fields. What i need is the 'sum' of to MEMO fields
> (concat) in a group by.
>

This is an adaptation of some SQL I have here, no guarantees but try it see
if it works for you. If you plan on using it repeatedly for large tables,
add the corresponding indexes to the memory tables in order to optimize the
joins. The field that is being concatenated is s1, a string, I believe
should work the same with a memo.

select * into memory\temp from mytable order by id;
alter table memory\temp add a autoinc, add lownum boolean default false, add
processed boolean default false;
select min(a) basenum, id  into memory\temp2
from memory\temp
group by id;
update memory\temp
 set lownum = true
 from memory\temp
 join memory\temp2 on basenum = a;
update  memory\temp m1
 set m1.processed = true, m1.s1 = m1.s1 + m2.s1
 from memory\temp
 join memory\temp m2 on m1.id = m2.id
 where m1.lownum = true and m2.lownum = false and m2.processed = false;
select * from memory\temp where lownum = true;

Robert

> Is it possible to add a custom function that does this?
>
> id    memo
> 1    text1
> 1    text2
> 1    text3
> 2    text1
> 2    text2
>
> result:
> 1, text1text2text3
> 2, text1text2
>
>
> Wierd Snieder
>