Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 3 of 3 total |
Concat memo in in Group By |
Mon, Dec 22 2008 10:49 AM | Permanent Link |
"Wierd Snieder" | 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 |
Mon, Dec 22 2008 2:18 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Dec 22 2008 3:39 PM | Permanent Link |
"Robert" | "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 > |
This web page was last updated on Monday, April 29, 2024 at 05:23 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |