Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Need help with SQL and Oracle 8i

Re: Need help with SQL and Oracle 8i

From: DA Morgan <damorgan_at_exesolutions.com>
Date: Mon, 13 Jan 2003 15:09:19 -0800
Message-ID: <3E23471F.CF723AE3@exesolutions.com>


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

Why does this require looping through anything?

Why wouldn't a simple SQL statement work with a sub-query based on program and max(datetime) with a group by?

PS: I deleted our cross-posting to c.d.o.miscellaneous 'cause I can take a hint.

Daniel Morgan Received on Mon Jan 13 2003 - 17:09:19 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US