Re: Need help with query in Oracle 8.1.7
Date: Fri, 24 Oct 2008 08:10:32 -0700 (PDT)
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
> > 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:
DEPTID CHAR(5) Additional columns:
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 /
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> 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