[RBASE-L] - Re: Merge 2 rows into one

Jason Kramer Thu, 25 Mar 2010 11:15:13 -0700

    This may not be the most elegant piece of code ever written, but if I understand your table structure correctly, I think that it will do what you want.
    If you want a sample DB, let me know.

SET VAR vnumdates INTEGER = NULL
SET VAR vnumtrans INTEGER = NULL
SET VAR vcurtrans INTEGER = NULL
SET VAR vcurnote NOTE = NULL
SET VAR vnewnote VARCHAR = NULL
SET VAR vcurdate DATE = NULL

SET WHILEOPT OFF
SELECT COUNT (DISTINCT trdate) INTO vnumdates FROM oldtrans
SELECT MIN (ADDDAY(trdate,-1)) INTO vcurdate FROM oldtrans
WHILE vnumdates <> 0 THEN
SELECT MIN trdate INTO vcurdate FROM oldtrans WHERE trdate > .vcurdate
SELECT COUNT (DISTINCT vernum) INTO vnumtrans FROM oldtrans WHERE trdate = .vcurdate
SELECT MIN (vernum - 1) INTO vcurtrans FROM oldtrans WHERE trdate = .vcurdate
SET VAR vnewnote = NULL
WHILE vnumtrans <> 0 THEN
    SELECT MIN vernum INTO vcurtrans FROM oldtrans WHERE trdate = .vcurdate AND vernum > .vcurtrans
    SELECT trnotes INTO vcurnote FROM oldtrans WHERE vernum = .vcurtrans
    IF vnumtrans = 1 THEN
      SET VAR vnewnote = .vnewnote + .vcurnote
    ELSE
      SET VAR vnewnote = .vnewnote + .vcurnote + (CHAR(10))
    ENDIF
    SET VAR vnumtrans = .vnumtrans - 1
ENDWHILE
SET VAR vnumdates = .vnumdates - 1
INSERT INTO newtrans (txdate,txnotes) VALUES (.vcurdate,.vnewnote)
ENDWHILE
SET WHILEOPT ON

CLEAR VARIABLES vnumdates,vnumtrans,vcurtrans,vcurnote,vnewnote,vcurdate

RETURN

Jason Kramer
University Archives and Records Management
002 Pearson Hall
(302) 831 - 3127 (voice)
(302) 831 - 6903 (fax)

On 3/25/2010 12:49 PM, MDRD wrote:

Jan

Yuk, Cursors scare me and I try to avoid them as much as possible.

I was hoping for a Project table that combined the notes to together.

Thanks

Marc

From: jan johansen

Sent: Thursday, March 25, 2010 10:43 AM

To: RBASE-L Mailing List

Subject: [RBASE-L] - Re: Merge 2 rows into one

Marc,

I've be scratching my head on this one. I don't think there is an eloquent way to do this.

I think you just need to brute force this with a cursor.

Run the cursor through your rows using the date. Concatenate the notes together until

the next date. Insert the new combined note into the new table.

Jan

-----Original Message-----
From: "MDRD" <[email protected]>
To: [email protected] (RBASE-L Mailing List)
Date: Thu, 25 Mar 2010 09:55:56 -0500
Subject: [RBASE-L] - Merge 2 rows into one

Hi

I have a table that has TrDate,TrNotes (note field), VerNum (autonum) and a few other column that may have 1,2 or 3 rows per day

We had 2-3 notes per day on some days because we went over the 4k limit.

Now I want to merge or combine those notes to a new Table that has a TxDate, TxNotes (Varchar column) ...

Is there an easy way to do this in one command? It seems so easy when I look at it but when I start to write the command on paper

my head starts spinning.

Thanks for any suggestion

Marc

[RBASE-L] - Re: Merge 2 rows into one

Reply via email to