Well, I'm guessin' that you'll be sleepin' easier and deeper tonight than the past few nights, even if you don't crack the top off a cold one!
Steve -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Bernard Lis Sent: Friday, April 25, 2008 1:09pm 13:09 To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Group by -- need another set of eyes Steve, Now that I recovered from this traumatic experience <g>, I think I will re-examine all other indexes in this database and either re-create them or change them to Primary Keys. Bernie Lis ----- Original Message ----- From: "Bernard Lis" <[EMAIL PROTECTED]> To: "RBASE-L Mailing List" <[email protected]> Sent: Friday, April 25, 2008 2:04 PM Subject: [RBASE-L] - Re: Group by -- need another set of eyes > Steve, > The way I understand it is that the old index carried forward from dos > 2.11 is not the same as a currently created one. > We converted from the dos version (6.5) to win 7.0 some time ago, then to > 7.1. My customer recently upgraded to 7.6 from 7.1 > I'm not sure what the difference is but I was so excited that it finally > worked that I forgot to question it any further. > Perhaps Razzak could chime in here and give a better detailed explanation. > > Bernie Lis > ----- Original Message ----- > From: "Wills, Steve" <[EMAIL PROTECTED]> > To: "RBASE-L Mailing List" <[email protected]> > Sent: Friday, April 25, 2008 1:55 PM > Subject: [RBASE-L] - Re: Group by -- need another set of eyes > > > Okay, Bernie, what did Razzak find with his "eagle eye"? > > Curiouser and curiouser, > Steve in Memphis > > > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Bernard > Lis > Sent: Friday, April 25, 2008 12:40pm 12:40 > To: RBASE-L Mailing List > Subject: [RBASE-L] - Re: Group by -- need another set of eyes > > PROBLEM SOLVED! > Thanks to Razzak's eagle eye. > Apparently it is a legacy problem. > I removed the index on CreditDate and the select statement came out > correct. > I then put the index back, and the select is still correct. > This database dates back to version 2.11 (remermber that one?) > > But thanks to all that chimed in on this one -- now we have something else > to look at when things don't seem to be right. > > Bernie Lis > > ----- Original Message ----- > From: Bill Downall > To: RBASE-L Mailing List > Sent: Friday, April 25, 2008 12:52 PM > Subject: [RBASE-L] - Re: Group by -- need another set of eyes > > Bernie, > > It may be noteworthy that you never get the duplicates unless you are > doing an aggregate action on CreditAmount. The problem may be more related > to the CreditAmount column than to the CreditDate column. Actually, COUNT > (*) is an aggregate function, but not one that needs a column. Try: > > SELECT CreditDate, COUNT (*), COUNT (CreditAmount) FROM Credits + > GROUP BY CreditDate > (The number will be less that count (*), and will be meaningless, because > it will count the number of distinct non-null dollar amounts in > creditamounts.) > > Also, before you do it, > > SET ZERO OFF > SET NULL '-0-' > > and do your queries again, see if that makes any difference. > > On Thu, Apr 24, 2008 at 7:44 PM, Bernard Lis <[EMAIL PROTECTED]> wrote: > Hi Bill, > I hope you can discern something from this: > ----- Original Message ----- > From: Bill Downall > To: RBASE-L Mailing List > Sent: Thursday, April 24, 2008 8:15 PM > Subject: [RBASE-L] - Re: Group by -- need another set of eyes > > Bernie, > > What do you get if you try these: > > SELECT DISTINCT CreditDate FROM Credits > CreditDate There are lots more going back to 1992 these are the last few > ---------- > 12/23/2007 > 01/27/2008 > 02/17/2008 > 02/24/2008 > 03/23/2008 > 03/30/2008 > > > SELECT CreditDate, COUNT (*) FROM Credits GROUP BY CreditDate > CreditDate COUNT (*) > ---------- ---------- > 12/23/2007 39 > 01/27/2008 28 > 02/17/2008 25 > 02/24/2008 44 > 03/23/2008 21 > 03/30/2008 33 > > SELECT CreditDate FROM Credits GROUP BY CreditDate > CreditDate > ---------- > 12/23/2007 > 01/27/2008 > 02/17/2008 > 02/24/2008 > 03/23/2008 > 03/30/2008 > > Do you get the doubled dates any of those ways? NO > > Also, just in case all those extra eyes might see something, please show > the results of: > LIST COLUMN CreditDate > list col creditdate > Column Name Table Name Attributes > ------------------ ------------------ --------------------------------------- > CreditDate Credits Type : DATE > Index : SINGLE-COLUMN > Comment: date of transaction > CRED Type : DATE > > LIST COLUMN CreditAmount > list col creditAmount > Column Name Table Name Attributes > ------------------ ------------------ --------------------------------------- > CreditAmount Credits Type : CURRENCY NOT NULL > Comment: dollar amount of transaction > CRED Type : CURRENCY > > LIST TABLE Credits > list table credits > > Table: Credits No Lock(s) > Descr: Credit transactions > > No. Column Name Attributes > --- ------------------ ------------------------------------------------------ > 1 CreditRefNo Type : INTEGER NOT NULL AUTONUMBER > Consrnt: PRIMARY KEY > Comment: Reference no > 2 OrderNo Type : INTEGER > Consrnt: FOREIGN KEY REFERENCES OrderHeader > Comment: order number > 3 CustomerNo Type : INTEGER > Consrnt: FOREIGN KEY REFERENCES Customer > Comment: customer number > 4 CreditDate Type : DATE > Index : SINGLE-COLUMN > Comment: date of transaction > 5 AccountNo Type : TEXT 4 > Comment: account number > 6 CreditAmount Type : CURRENCY NOT NULL > Comment: dollar amount of transaction > 7 CreditComment Type : TEXT 30 > Comment: comment > 8 CreditUser Type : TEXT 18 > Comment: last modified by > 9 CreditDateTime Type : DATETIME > Comment: date/time last modified > Current number of rows: 9420 > > SHOW DATE > SHOW ZERO > SHOW NULL > > R>show date > DATE format mm/dd/yyyy > DATE sequence MMDDYY > Century threshold YEAR is 50 > Default CENTURY is 19 > > R>show zero > (ZERO ) ON Treat nulls as ZERO in numeric calculations > > R>show null > NULL symbol > > Bill > > On Thu, Apr 24, 2008 at 4:56 PM, Bernard Lis <[EMAIL PROTECTED]> wrote: > Right on John, but for the record CreditDate is not indexed. > ----- Original Message ----- From: "John Engwer" <[EMAIL PROTECTED]> > To: "RBASE-L Mailing List" <[email protected]> > Sent: Thursday, April 24, 2008 10:23 AM > Subject: [RBASE-L] - Re: Group by -- need another set of eyes > > Larry, he created a temp table and ran the select from the temp table. > That > should have eliminated the index possibility. > > John > > -----Original Message----- > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Lawrence > Lustig > Sent: Thursday, April 24, 2008 9:56 AM > To: RBASE-L Mailing List > Subject: [RBASE-L] - Re: Group by -- need another set of eyes > > Bernie: > > I'll bet dollars to donuts this is an indexing issue. > > Is CreditDate indexed? If so, try dropping the index and repeating the > command (which will run more slowly). In fact, try projecting a completely > unindexed copy of the table and operating on that. > > Also, look for CreditDate used in a multi-column index. > > -- > Larry > > >
