Home » SQL & PL/SQL » SQL & PL/SQL » Problem with Query  () 1 Vote
Problem with Query [message #221441] Mon, 26 February 2007 15:02 Go to next message
Messages: 1
Registered: February 2007
Junior Member
hello all,
i have a fairly simple query but the results dosent seems to come properly.
here is the query

SELECT count(T4.State) , T13.id, trunc(T4.Action_timestamp) , T13.name, T4.State FROM Req T1, Project T13, History T4 WHERE T1.pid= T13.id and T1.id = T4.entityid ad T4.Action_timestamp in ((Select max(T14.Action_timestamp) from history t14,Req T15 where t14.entityid = T1.id and T1.id =T15.id and T14.Action_timestamp between to_date('20030101000000', 'YYYYMMDDhh24miss') and to_date('20071231235959', 'YYYYMMDDhh24miss'))) Group by T13.id, trunc(T4.Action_timestamp) , T13.name, T4.State ORDER BY trunc(T4.Action_timestamp) asc

This query will retun the records for each day the state and its count for each project

The problem i face is:
1. When i give the date range from 2003/01/01 to 2007/02/26
Many data is omitted.
2. When i provide the date range for example from 2006/01/01 to 2006/12/31 i get the datas which are omitted by the above date ranges.

I am not able to find out the root cause of this problem. Has any one came across this kind of situation?
the reason i am doing group by is to avoid getting more than 10000 records and process them individually.

I also tried removing the between clause and using > < but still the problem exits of bigger date ranges.

Any suggestion would be of great help to me

Thanks in advance

Re: Problem with Query [message #221457 is a reply to message #221441] Mon, 26 February 2007 21:55 Go to previous message
Messages: 1848
Registered: November 2006
Senior Member
First of all, formatting a query is a great thing as it does not seem like a mess of characters and it is much better and faster to understand its content. Well, you issue
SELECT trunc(T4.Action_timestamp), <...>
FROM Req T1, Project T13, History T4
WHERE T1.pid = T13.id AND T1.id = T4.entityid 
 AND T4.Action_timestamp in (SELECT max(T14.Action_timestamp)
   FROM history t14, Req T15
   WHERE t14.entityid = T1.id AND T1.id = T15.id 
     AND T14.Action_timestamp BETWEEN <min_limit> AND <max_limit>)
GROUP BY trunc(T4.Action_timestamp), <...>
ORDER BY trunc(T4.Action_timestamp) asc

In your inner select you get MAXIMAL Action_timestamp for given row (only ONE, no need to use IN, '=' will satisfy) in given range.

If you change the range limit, you will most probably get other Action_timestamp, so consequently different rows are selected from T4. Try it by running the inner select separately and then propagating its result into the main query.
Previous Topic: Difference between Aggregate & Materialized View
Next Topic: Adjust to local time
Goto Forum:

Current Time: Wed Aug 16 08:26:52 CDT 2017

Total time taken to generate the page: 0.09023 seconds