Re: Need help with SQL and Oracle 8i

From: Karsten Farrell <kfarrell_at_belgariad.com>
Date: Tue, 14 Jan 2003 00:01:19 GMT
Message-ID: <jrIU9.178$MT2.10013919@newssvr15.news.prodigy.com>

Tony wrote:
> Using Oracle 8i
>
> 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!
>

Maybe something like:

select j.program "Program"

      , j.description "Description"
      , j.last_complete_time "Last Complete"
      , j.last_run_time "Last Run"
      , to_char(ce.client_expectation,'hh:mi') "Deadline"
      , j.report_type "Type"
from   jobs j
      , client_expectation ce

where ce.program = j.program

and    j.report_type = '2'
and    j.last_complete_time is not null
and    j.last_complete_time =
        (
          select max(jobs.last_complete_time)
          from   jobs
          where  jobs.report_type = '2'
          and    jobs.last_complete_time is not null
          and    jobs.last_complete_time > trunc(sysdate)
        )

order by j.program;

Of course, I haven't run this to see how it performs. If the Jobs and Client_Expectation tables are large, you wouldn't want to do it this way.

I suspect this isn't the real SQL anyhow, since you print out report_type and have limited it to always be equal to '2'. Received on Mon Jan 13 2003 - 18:01:19 CST