Thanks Bill I will try that in the AM Marc
From: Bill Downall Sent: Thursday, March 25, 2010 3:47 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Merge 2 rows into one You don't indicate what other columns are represented by the *, but here's one possibility: CREATE TEMP VIEW ptsoapGroup2 AS + SELECT CustNum, SpDate, othercolumns + FROM ptSoup + GROUP BY CustNum, SpDate, exactlythesameothercolumns + HAVING COUNT (*) = 2 PROJECT TEMP ptSoap2 FROM ptSoapGroup2 USING * Bill On Thu, Mar 25, 2010 at 4:35 PM, MDRD <[email protected]> wrote: I keep getting a Syntax error on this, it looks good to me? project Temporary ptsoap2 from ptsoap using * where (select custnum,spdate, count(*) from ptsoap group by custnum,spdate having count(*) =2 ) Thanks Marc From: Emmitt Dove Sent: Thursday, March 25, 2010 1:59 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Merge 2 rows into one I’m still waiting for someone to develop a DWIMNWIS system. (Do What I Mean, Not What I Say) Emmitt Dove Manager, Converting Applications Development Evergreen Packaging, Inc. [email protected] (203) 214-5683 m (203) 643-8022 o (203) 643-8086 f [email protected] From: [email protected] [mailto:[email protected]] On Behalf Of MDRD Sent: Thursday, March 25, 2010 14:49 To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Merge 2 rows into one 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 KramerUniversity Archives and Records Management002 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
