Search code examples
oracle-databasesubquerycorrelated-subquery

Correlated SQL Query using With Clause


I am trying to modify below query :

SELECT START_END.*, START_END.LOAD_TIME_END - START_END.LOAD_TIME_START
FROM
(
  SELECT START.JOB_ID, START.LOAD_TIME_START, END.LOAD_TIME_END
  FROM
  (
    SELECT JOB_ID,LOAD_TIME AS LOAD_TIME_START
    FROM JOB_CTRL_RECON JCR
    INNER JOIN
    (
      SELECT JOB_ID AS JOB_ID_S, MAX(RECON_ID) AS S_MAX_RECON_ID
      FROM job_ctrl_recon
      where job_id in ('1','2')
      and count_type = 'Source'
      GROUP BY JOB_ID
    ) SMAX
    ON JCR.JOB_ID = SMAX.S_MAX_RECON_ID
    AND JCR.RECON_ID = SMAX.RECON_ID
  ) START
  INNER JOIN
  (
    SELECT JOB_ID,LOAD_TIME AS LOAD_TIME_END FROM JOB_CTRL_RECON JCR INNER JOIN 
    (
      SELECT JOB_ID AS JOB_ID_T, MAX(RECON_ID) AS T_MAX_RECON_ID
      FROM job_ctrl_recon
      where job_id in ('1','2')
      and count_type = 'Target'
      GROUP BY JOB_ID
    ) TMAX
    ON JCR.JOB_ID = TMAX.T_MAX_RECON_ID
    AND JCR.RECON_ID = TMAX.RECON_ID
  ) END
  ON START.JOB_ID = END.JOB_ID
) START_END

to a query using with clause as below :

How can i eliminate the multiple use of where job_id in ('1','2') using with clause something like below :

WITH A AS 
(
  SELECT JOB_ID FROM JOB_CTRL_RECON WHERE JOB_ID IN ('60','67')
)
SELECT START_END.*, START_END.LOAD_TIME_END - START_END.LOAD_TIME_START
FROM 
(
  SELECT START.JOB_ID, START.LOAD_TIME_START, END.LOAD_TIME_END
  FROM
  (
    SELECT JOB_ID,LOAD_TIME AS LOAD_TIME_START
    FROM JOB_CTRL_RECON JCR
    INNER JOIN 
    (
      SELECT JOB_ID AS JOB_ID_S, MAX(RECON_ID) AS S_MAX_RECON_ID
      FROM job_ctrl_recon
      where job_id in (????)
      and count_type = 'Source'
      GROUP BY JOB_ID
    ) SMAX
    ON JCR.JOB_ID = SMAX.S_MAX_RECON_ID
    AND JCR.RECON_ID = SMAX.RECON_ID
  ) START
  INNER JOIN 
  (
    SELECT JOB_ID,LOAD_TIME AS LOAD_TIME_END
    FROM JOB_CTRL_RECON JCR
    INNER JOIN 
    (
      SELECT JOB_ID AS JOB_ID_T, MAX(RECON_ID) AS T_MAX_RECON_ID
      FROM job_ctrl_recon
      where job_id in (????)
      and count_type = 'Target'
      GROUP BY JOB_ID
    ) TMAX
    ON JCR.JOB_ID = TMAX.T_MAX_RECON_ID
    AND JCR.RECON_ID = TMAX.RECON_ID
  ) END
  ON START.JOB_ID = END.JOB_ID
) START_END, A
WHERE START_END.JOB_ID = A.JOB_ID

Solution

  • Instead of WITH, you may be able to simplify the code using a KEEP with an analytic function. The code is a bit weird at first, but it lets you only specify the JOB_ID once and it only has to read from the table once.

    --Load time start and end for each JOB_ID, based on the latest RECON_ID.
    select
        job_id,
        max(load_time_start) load_time_start,
        max(load_time_end) load_time_end,
        max(load_time_end) - max(load_time_start) diff
    from
    (
        --First and last load time for each set of rows, with some debug data.
        select job_id, count_type, recon_id, load_time
            ,last_value(case when count_type = 'Source' then load_time else null end)
                over (
                    partition by job_id, count_type
                    order by recon_id
                    rows between unbounded preceding and unbounded following
                ) load_time_start
            ,last_value(case when count_type = 'Target' then load_time else null end)
                over (
                    partition by job_id, count_type
                    order by recon_id
                    rows between unbounded preceding and unbounded following
                ) load_time_end
        from job_ctrl_recon
        where job_id in (1,2)
    )
    group by job_id
    order by job_id;
    

    For this table and data:

    create table job_ctrl_recon(job_id number, recon_id number, count_type varchar2(100), load_time date);
    
    alter session set nls_date_format = 'DD-Mon-RR';
    
    insert into job_ctrl_recon
    select 1, 400, 'Source', '24-Feb-18' from dual union all
    select 1, 40,  'Source', '23-Feb-18' from dual union all
    select 1, 89,  'Target', '25-Feb-18' from dual union all
    select 1, 8,   'Target', '25-Feb-18' from dual union all
    select 2, 700, 'Source', '24-Feb-18' from dual union all
    select 2, 8,   'Source', '23-Feb-18' from dual union all
    select 2, 567, 'Target', '25-Feb-18' from dual union all
    select 2, 45,  'Target', '25-Feb-18' from dual union all
    select 2, 678, 'Target', '25-Feb-18' from dual;
    
    commit;
    

    These are the results:

    JOB_ID   LOAD_TIME_START   LOAD_TIME_END   DIFF
    ------   ---------------   -------------   ----
    1        2018-02-24        2018-02-25      1
    2        2018-02-24        2018-02-25      1