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
