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
