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
