A question about joining and group by clause

[aarnone]

I have this query below which works but I need to modify it so I get a more precise result:

Select a1.JOB_SCHED_ID,
a1.JOB_SCHED_NAME, a1.JOB_ENABLED_FLAG, a1.JOB_SCHED_TYPE,
a1.ONE_TIME_FLAG, a1.ONE_TIME_DATETIME,
a1.DAILY_FLAG, a1.DAILY_FROM_TIME, a1.DAILY_TO_TIME, a1.DAILY_FREQUENCY_IN_MIN,
a1.WEEKLY_FLAG, a1.WEEKLY_TIME, a1.WEEKLY_DAYOFWEEK,
a1.MONTHLY_FLAG, a1.MONTHLY_DAYOFMONTH, a1.MONTHLY_TIME,
a1.EMAIL_ON_ERROR, a1.EMAIL_ON_SUCCESS, a1.DATE_DELETED,
a2.JOB_LOG_ID, a2.JOB_SCHED_ID, a2.START_DATE, a2.END_DATE, a2.JOB_STATUS_ID,
a2.FAILED_JOB_STEP_NUMBER, a2.DATE_DELETED, a2.ERROR_MESSAGE, a3.JOB_STATUS_DESC,
a4.JOB_STEP_ID, a4.JOB_STEP_NAME, a5.REQUEST_ID, a5.DATETIME_PROCESSED
from SCHDLR_JOB_TBL a1, SCHDLR_JOB_LOG_TBL a2, SCHDLR_STATUS_TBL a3, SCHDLR_JOB_STEP_TBL a4, SCHDLR_JOB_REQUEST_TBL a5
where a1.JOB_SCHED_ID = a2.JOB_SCHED_ID (+)
and a2.JOB_STATUS_ID = a3.JOB_STATUS_ID (+)
and a1.JOB_SCHED_ID = a4.JOB_SCHED_ID AND a4.DATE_DELETED is null
and a1.JOB_SCHED_ID = a5.JOB_SCHED_ID (+)

The problem with the above query is that the schdlr_job_log_tbl can contain one to many entries for each job record in the schdlr_job_tbl, so what I need to do is only select the latest record from the log_tbl based on the end_date. Now I know how to do this with the following query, but I dont know how to incorporate the two.

SELECT x.job_sched_id, X.END FROM (SELECT JOB_SCHED_ID, MAX(NVL(END_DATE,SYSDATE)) "END" FROM SCHDLR_JOB_LOG_TBL GROUP BY JOB_SCHED_ID) X
WHERE x.job_sched_id=1

 

[r937]

select ...
  from SCHDLR_JOB_TBL a1
left outer
  join SCHDLR_JOB_LOG_TBL a2
    on a1.JOB_SCHED_ID = a2.JOB_SCHED_ID
[b]   and a2.END_DATE
     = ( select max(END_DATE)
           from SCHDLR_JOB_LOG_TBL
          where JOB_SCHED_ID = a1.JOB_SCHED_ID )[/b]
left outer
  join SCHDLR_STATUS_TBL a3
    on a2.JOB_STATUS_ID = a3.JOB_STATUS_ID
left outer
  join SCHDLR_JOB_STEP_TBL a4
    on a1.JOB_SCHED_ID = a4.JOB_SCHED_ID
   and AND a4.DATE_DELETED is null
left outer
  join SCHDLR_JOB_REQUEST_TBL a5  
    on a1.JOB_SCHED_ID = a5.JOB_SCHED_ID

r937.com | rudy.ca

 

[aarnone]

When I try to run the query above, I am getting an error saying that - a column may not be outer joined to a subquery.

 

[r937]

well, unless i've overlooked something, my solution is valid ANSI SQL

perhaps you might wish to post your question in the appropriate forum for whatever database system you're running

r937.com | rudy.ca

 

[JarlH]

You've got an double AND:
" and AND a4.DATE_DELETED is null "

 

[r937]

ah, the perils of copy/paste, thanks Jarl

r937.com | rudy.ca

 

[aarnone]

i am running this against oracle 9i, could this be why it's throwing the eror.

 

[r937]

will oracle allow you to inner join a column to a subquery? just change the first LEFT OUTER to INNER and see if it runs

if it does, then your query will no longer return any SCHDLR_JOB_TBL rows that have no matching SCHDLR_JOB_LOG_TBL rows whatsoever

r937.com | rudy.ca

 

[aarnone]

even if it does work I need to return the job table records if no log records exist so I need to do an outer join.

 

[r937]

well, i guess you will simply have to join to all LOG rows, move the max condition to the WHERE clause, and filter out all but the max row there

and don't forget to allow NULLs because of the left outer join



r937.com | rudy.ca

 

[aarnone]

Any chance you can give me an example of that?

 

[dnoeth]

In Oracle 9 you can use an Analytic Function:

Add a new column to your query:

  row_number() over (partition by a1.JOB_SCHED_ID
                     order by END_DATE desc) as rn

and then

select *
from
 (your query + new col
 ) x
where rn = 1

Dieter