Re: Need help with SQL and Oracle 8i

On Mon, 13 Jan 2003 23:01:30 GMT, Tony <none_at_none.com> wrote:

>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!

I'm thinking I need to use the Distinctive and loop keywords, but not sure how to impliment that in my sql code above. :(

-Tony!-
. Received on Mon Jan 13 2003 - 17:05:49 CST