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 <[email protected]> > *Sent:* Thursday, March 25, 2010 1:59 PM > *To:* RBASE-L Mailing List <[email protected]> > *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 <[email protected]> > > *Sent:* Thursday, March 25, 2010 1:14 PM > > *To:* RBASE-L Mailing List <[email protected]> > > *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 <[email protected]> > > *Sent:* Thursday, March 25, 2010 10:43 AM > > *To:* RBASE-L Mailing List <[email protected]> > > *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 > > > >
