-
select trunc(STAT_TIMESTAMP) "Day", count(*) "No. Stats"
from RACE_STATISTICS
group by trunc(STAT_TIMESTAMP)
/
does not work? it works for me
-
Yes, thanks Pando that does work in terms of summarizing the data, but as mentioned previously I then need access to the original records that lie beneath that summary. So for example say your code returns the following info:
DATE | Count(*)
17/10/1997 | 45
18/10/1997 | 2
This would suggest that possibly something went wrong on 17/10/1997 to generate so much info. Therefore I need a way to get to those 45 records in order to see what they were actually recording.
-
well then obviously you have to use two queries, I thought you just wanted the summary In one query to get the summary and the description is kind of stupid anyway though, you will see like 45 for all descriptions unless you group again like
Code:
select
trunc(STAT_TIMESTAMP) "Day",
STAT_DTO_ID,
STAT_I1,
STAT_I2,
count(*) "No. Stats"
from RACE_STATISTICS
group by trunc(STAT_TIMESTAMP), STAT_DTO_ID,
STAT_I1, STAT_I2
/
and you have to create function based index if you plan to pass as a parameter to Oracle the timestamp otherwise it will do full table scan
to use function based index you have to add this to initSID.ora
QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED
bounce database
There is a bug in 8.1.5 trunc cant work with function based indexes, fixed in 8.1.6
This is about 8i.... not sure other versions
[Edited by pando on 09-18-2001 at 06:18 AM]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|