Re: Need help with query in Oracle 8.1.7

On Oct 24, 9:29 am, "gym dot scuba dot kennedy at gmail" <kenned..._at_verizon.net> wrote:
> "Brian K" <mailregd..._at_gmail.com> wrote in message
>
> news:gEkMk.117894$sg2.86431_at_fe09.news.easynews.com...
>
>
>
>
>
> > 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.
>
> It isn't date formatted it is a string representing a date - a classicly bad
> design decision.
> Jim- Hide quoted text -
>
> - Show quoted text -

No, it's a date as CENUPD is a DATE column:

"The query in question runs against a table with five columns:

Primary key:
FACID CHAR(10)
DEPTID CHAR(5) Additional columns:
CENUPD DATE
CENUPT CHAR(8)
CENCOUNT CHAR(10) There are a few hundred thousand total rows in this table."

The time isn't, and that *is* a design flaw. The time can be 'reinstated' to the date value:

SQL> create table five_cols(
  2 FACID CHAR(10) ,
  3 DEPTID CHAR(5) ,
  4 CENUPD DATE ,
  5 CENUPT CHAR(8) ,
  6 CENCOUNT CHAR(10) ,
  7 constraint five_cols_pk primary key(facid, deptid))   8 /

Table created.

SQL>
SQL> insert all
  2 into five_cols
  3 values('Test','ACT',trunc(sysdate-10), to_char(sysdate-10, 'HH24:MI:SS'), 7)
  4 into five_cols
  5 values('Test', 'ACG',trunc(sysdate-9), to_char(sysdate-9, 'HH24:MI:SS'),11)
  6 into five_cols
  7 values('Test2','BILL',trunc(sysdate-8), to_char(sysdate-8, 'HH24:MI:SS'),73)
  8 into five_cols
  9 values('Test3', 'PRG',trunc(sysdate-7), to_char(sysdate-7, 'HH24:MI:SS'), 44)
 10 select * from dual;

4 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select facid, deptid,
  2 to_char(to_date(to_char(cenupd, 'MM-DD-YYYY')||cenupt, 'MM- DD-YYYY HH24:MI:SS'), 'DD-MON-RRRR HH24:MI:SS') UPD_DT,   3 cencount
  4 from five_cols;

FACID DEPTI UPD_DT CENCOUNT

---------- ----- -------------------- ----------
Test       ACT   14-OCT-2008 10:08:54 7
Test       ACG   15-OCT-2008 10:08:54 11
Test2      BILL  16-OCT-2008 10:08:54 73
Test3      PRG   17-OCT-2008 10:08:54 44

SQL> As illustrated, it's a bit ... convoluted ... to do so, but it is possible.

David Fitzjarrell Received on Fri Oct 24 2008 - 10:10:32 CDT