Re: Need help with query in Oracle 8.1.7

Brian K <mailregdump_at_gmail.com> writes:

> On Fri, 24 Oct 2008 06:39:42 -0700, gazzag wrote:
>
>> On 24 Oct, 13:23, "Shakespeare" <what..._at_xs4all.nl> wrote:
>>> ============================================== Wouldn't a select max( )
>>> group by
>>>
>>> be enough to do the job?
>>>
>>> Shakespeare- Hide quoted text -
>>>
>>> - Show quoted text -
>>
>> Insufficient data :)
>>
>> I was wondering where PL/SQL came into it.
>>
>> -g
>
> I will post schema and some sample data.
>
> The main problem I'm having is that the date field is date formatted, but
> they neglected to include the actual time.
>
> So, when I'm trying to do date/time functions I invariably end up trying
> to parse the date and time as something like '10-Oct-08 00:00:00
> 12:31:56'.
>
> Well, the main problem is actually my complete ignorance of oracle
> functions, but that's beside the point.

Actually, I think it may be the point!

Have a look at the Oracle SQL reference - in particular, look at the section on the built-in DATE type, date format models, date comparisons and the date related functions.

I know you may feel that Oracle is big and huge and you have too little time to look at the manuals, but in reality, you will save lots of time. In fact, I suspect 1 hour with the manual and 20 minutes interaction with sqlplus and you will have the job done!

Note that while Oracle does have Date and various timestamp formats, don't be mislead to think that the date value doesn't have time components (hrs, min, sec). It does. If your example above is real regarding the date you have to parse, then it is likely that the time information *was* included with the values when inserted (if you insert just a date value witnout any time information, depending on what functions were used, either the time information defaults to 00:00:00 or to the current time when the date was set. Given your example doesn't have 00:00:00 for time components, you can probably assume that if you have two date values for the same day that the one with the latest time value was the later value. Your only other choice is to assume that the time component cannot be trusted and that any value with the same date information is in fact equal i.e. occurred at the same time, because there is nothing else to state otherwise.

Note that Oracle dates follow a 'sane' semantics with respect to comparison operations. 25-OCT-2008 < 26-OCT-2008 and 25-OCT-2008 00:00:00 < 25-OCT-2008 00:00:01 etc, so something like select max(rec_date) from t1 will return the maximum rec_date value from all the rows in t1.

In fact, the most common 'error' I see in sQL code is the fact people forget that Oracle date objects contain time information. I often see something like

select *
from table1
where colum1 = sysdate

where the intention is to retrieve all the records that have todays date. The problem is that this query will only return the records that equal sysdae, which has todays date AND time. To solve this problem, Oracle has functions like 'trunc' and 'to_date' that give you the control you need in such comparisons.

Also keep in mind that the way Oracle displays the date value isn't necessarily the same as what is stored in the column. There is a default date format model applied when Oracle does a implicit conversion to display the value. So, if you select date fields from a table in sqlplus and you only see something like DD-MON-YYYY e.g. 25-OCT-2008, this doesn't mean that there is no time information stored in the column - it just means that the default format only displays the date information.

One way to get a handle on this is to select from 'dual e.g.

select sysdate from dual;

select to_char(sysdate, 'DD-MON-YYYY H24:MI:SS) from dual;

select trunc(sysdate) from dual;

etc.

Finally, unless I've missed something in what you are trying to do, I don't think you need PL/SQL. You should be able to achieve what you need with just a simple SQL.

-- 
tcross (at) rapttech dot com dot au