Well I need Gibbs to slap me up side the head, like my dad use to smack the TV to shake the tubes and "fix" our TV
The Project works by it self and the Select works by itself but together I get an error Syntax incorrect I know exactly what I want so what's the catch? 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
