Need help with SQL and Oracle 8i

From: Tony <none_at_none.com>
Date: Mon, 13 Jan 2003 23:01:30 GMT
Message-ID: <emg62v4mp78ekp8tl176efjahp1372os8c@4ax.com>

Using Oracle 8i

We have a report server which runs a bunch of reports for a bunch of clients. The reports and various info about each report are listed in a "jobs" table, There is a client_expectation table that list each project (client) and it's report deadline time.

I wrote the sql below to give me times for an on-time report on the reports I am doing daily.

What I would like to do is have the sql run through each client ("program" in the jobs table) and output just one result for each client showing the latest report completion time (from last_complete_time in the jobs table).

Right now I have it listing all the reports ran for each program, sorted so that the latest is on the bottom of each group.. Good, but would like to streamline it further.

---

select jobs.PROGRAM "Program", jobs.description "Description", jobs.LAST_COMPLETE_TIME "Last Complete", jobs.LAST_RUN_TIME "Last Run",
to_char(client_expectation.client_expectation,'hh:mi') "Deadline",jobs.report_type "Type"
from jobs, client_expectation WHERE
(jobs.PROGRAM=client_expectation.PROGRAM) and (Jobs.report_type='2'
and jobs.last_complete_time > trunc(sysdate)) and jobs.last_complete_time is not null
Group by jobs.Program, jobs.Description, jobs.Last_complete_time,jobs.scheduled_time, jobs.last_run_time, jobs.report_type, client_expectation.CLIENT_EXPECTATION order by jobs.program, last_complete_time;

---

Any help on looping through the tables programs would be appreciated. I'm still pretty much a newbie, so type slowly :)

Tony! Received on Mon Jan 13 2003 - 17:01:30 CST