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

[MDRD]

Thanks Jason

I was thinking it would take a While within a While, which would take me a 
While.

So, at lunch I was thinking about projection a temp table where the Count of 
Trdate = 1
insert those  rows, then project Temp Table where Count = 2, then add that 2nd 
note to the first one
and so on.  I do not think there will ever be more than 3 rows per date.

Now, if only Razzak could make RBase like that computer on Star Trek, I could 
just tell the computer what I want
Hint... Hint

Marc




From: Jason Kramer 
Sent: Thursday, March 25, 2010 1:14 PM
To: RBASE-L Mailing List 
Subject: [RBASE-L] - Re: Merge 2 rows into one


    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" <mdrd...@sbcglobal.net>
    To: rbase-l@rbase.com (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