Header Ads

  • Recent Post

    OUTER JOIN ON Effective Dated(EFFDT) SUBQUERIES

    If a table has effdt or any date as a key field and if the requirement is such that to fetch latest information from this table we can simply write a query that fetches latest data using a subquery.
    For example to fetch data from PS_NAMES table which must be latest information as of today we know to write a simple SQL like below:
    SELECT N.NAME
    FROM PS_NAMES N
    WHERE N.EFFDT= (SELECT MAX(N_ED.EFFDT)
                                 FROM PS_NAMES N_ED
                                 WHERE N_ED.NAME_TYPE=N.NAME_TYPE
                                 AND N_ED.EMPLID = N.EMPLID
                                 AND N_ED.EFFDT <=SYSDATE)

    Now let's add another requirement:
    If the requirement is to fetch NAME for every emplid in JOB record then the below SQL would look like the below:
    SELECT J.EMPLID, N.NAME
    FROM PS_JOB J, PS_NAMES N
    WHERE
    J.EFFDT = (SELECT MAX(J_ED.EFFDT) FROM PS_JOB J_ED
                       WHERE J.EMPLID = J_ED.EMPLID
                       AND J.EMPL_RCD = J_ED.EMPL_RCD
                       AND J_ED.EFFDT <= SYSDATE)
    AND J.EFFSEQ =(SELECT MAX(J_ES.EFFSEQ) FROM PS_JOB J_ES
                                  WHERE J.EMPLID = J_ES.EMPLID
                                  AND J.EMPL_RCD = J_ES.EMPL_RCD
                                  AND J.EFFDT = J_ES.EFFDT)
    AND J.EMPLID = N.EMPLID
    AND N.EFFDT= (SELECT MAX(N_ED.EFFDT)
                                 FROM PS_NAMES N_ED
                                WHERE N_ED.NAME_TYPE=N.NAME_TYPE
                                 AND N_ED.EMPLID = N.EMPLID
                                 AND N_ED.EFFDT <= SYSDATE)

    If the requirement is like NAMES record may not have all the emplids in JOB table then how to write the query? The answer is simple - an OUTER JOIN. However, Oracle does not allow outer join on a subquery which will prevent us from writing an outer join effdt subquery.
    To achieve the desired results, we can write the query as below in a slightly different way:
    SELECT J.EMPLID, N.NAME
    FROM PS_JOB J, PS_NAMES N
    WHERE
    J.EFFDT = (SELECT MAX(J_ED.EFFDT) FROM PS_JOB J_ED
                       WHERE J.EMPLID = J_ED.EMPLID
                       AND J.EMPL_RCD = J_ED.EMPL_RCD
                       AND J_ED.EFFDT <= SYSDATE)
    AND J.EFFSEQ =(SELECT MAX(J_ES.EFFSEQ) FROM PS_JOB J_ES
                                  WHERE J.EMPLID = J_ES.EMPLID
                                  AND J.EMPL_RCD = J_ES.EMPL_RCD
                                  AND J.EFFDT = J_ES.EFFDT)
    AND J.EMPLID = N.EMPLID (+)
    AND (N.EFFDT IS NULL
             OR N.EFFDT= (SELECT MAX(N_ED.EFFDT)
                                       FROM PS_NAMES N_ED
                                       WHERE N_ED.NAME_TYPE=N.NAME_TYPE
                                       AND N_ED.EMPLID = N.EMPLID
                                       AND N_ED.EFFDT <=SYSDATE))

    Now let's another complexity to the requirement. If the requirement is such that to fetch NAME of an emplid as of JOB table's EFFDT and not the latest data in NAMES table.
    The SQL in straight forward scenario will be to replace sysdate condition in NAMES table effdt query to J.EFFDT instead of SYSDATE. However for an emplid, if JOB table has latest EFFDT as 01-JAN-2009 and NAMES table has data only on 12-JUL-2009, the above query will drop of the emplid. Because NAMES table has data for this emplid (so NULL condition becomes false) and the row in NAMES table gets dropped as there is no data in NAMES table as on 1st Jan. If JOB has an EFFDT on or after 12th July for this emplid then the query would fetch results. If we have to have an outer join as above the query is not sufficient. So we modify the query further as below.
    SELECT J.EMPLID, N.NAME
    FROM PS_JOB J, PS_NAMES N
    WHERE
    J.EFFDT = (SELECT MAX(J_ED.EFFDT) FROM PS_JOB J_ED
                       WHERE J.EMPLID = J_ED.EMPLID
                       AND J.EMPL_RCD = J_ED.EMPL_RCD
                       AND J_ED.EFFDT <= SYSDATE)
    AND J.EFFSEQ =(SELECT MAX(J_ES.EFFSEQ) FROM PS_JOB J_ES
                                  WHERE J.EMPLID = J_ES.EMPLID
                                  AND J.EMPL_RCD = J_ES.EMPL_RCD
                                  AND J.EFFDT = J_ES.EFFDT)
    AND J.EMPLID = N.EMPLID (+)
    AND (N.EFFDT IS NULL
             OR (SELECT MAX(N_ED.EFFDT)
                    FROM PS_NAMES N_ED
                    WHERE N_ED.NAME_TYPE=N.NAME_TYPE
                    AND N_ED.EMPLID = N.EMPLID
                    AND N_ED.EFFDT <= J.EFFDT) IS NULL
             OR N.EFFDT=(SELECT MAX(N_ED.EFFDT)
                                      FROM PS_NAMES N_ED
                                      WHERE N_ED.NAME_TYPE=N.NAME_TYPE
                                      AND N_ED.EMPLID = N.EMPLID
                                      AND N_ED.EFFDT <= J.EFFDT))

    3 comments:

    1. Hi Shyam,
      Thanks a lot for writing such useful blogs.These are really helpful.
      Keep it up.

      ReplyDelete
    2. Thank you for the example, it is really helpful!
      I was able to reference your example and made my query to work. Thanks again!

      ReplyDelete
    3. Hi Shyam, Your code is better than other forums on this issue, however it needs one more step. Continuing your example, we need to decide if an employee with a future NAMES record should show their Future Name or null. I'll label the scenario where we want to pull the future name as Scenario A, and the scenario where we do not want to pull the future name as Scenario B. Your code will need to be expanded in either of these scenarios.

      Scenario A: We want to pull the employee's future name. This does not appear to be your requirement since you state 'fetch NAME of an emplid as of JOB table's EFFDT', however your code WILL return the future name. Worse than that, if the employee has multiple future NAME effective dated rows, you will return ALL of those future rows instead of just one name for the employee. In real life this should not happen for an employee's name/department/jobcode/company, but it could happen for future items such as addresses/awards/training, especially if you are looking at a historical job row instead of the max. 'distinct' won't work here since we'll assume the multiple future names are different, so the solution for Scenario A will be to grab only the MIN EFFDTed future name row. I chose MIN since it is the name most closely tied to your job's effective date. So the code for Scenario A must be changed as follows (my code is in lowercase):

      SELECT J.EMPLID, N.NAME
      FROM PS_JOB J, PS_NAMES N
      WHERE
      J.EFFDT = (SELECT MAX(J_ED.EFFDT) FROM PS_JOB J_ED
      WHERE J.EMPLID = J_ED.EMPLID
      AND J.EMPL_RCD = J_ED.EMPL_RCD
      AND J_ED.EFFDT <= SYSDATE)
      AND J.EFFSEQ =(SELECT MAX(J_ES.EFFSEQ) FROM PS_JOB J_ES
      WHERE J.EMPLID = J_ES.EMPLID
      AND J.EMPL_RCD = J_ES.EMPL_RCD
      AND J.EFFDT = J_ES.EFFDT)
      AND J.EMPLID = N.EMPLID (+)
      AND (N.EFFDT IS NULL
      OR ((SELECT MAX(N_ED.EFFDT)
      FROM PS_NAMES N_ED
      WHERE N_ED.NAME_TYPE=N.NAME_TYPE
      AND N_ED.EMPLID = N.EMPLID
      AND N_ED.EFFDT <= J.EFFDT) IS NULL and n.effdt=(select min(n1.effdt) from ps_names n1 where n1.name_type=n.name_type and n1.emplid=n.emplid))
      OR N.EFFDT=(SELECT MAX(N_ED.EFFDT)
      FROM PS_NAMES N_ED
      WHERE N_ED.NAME_TYPE=N.NAME_TYPE
      AND N_ED.EMPLID = N.EMPLID
      AND N_ED.EFFDT <= J.EFFDT))




      Scenario B: We do NOT want to pull the employee's future name since it was not in effect at the time of our JOB record. This appears to be your requirement since you state 'fetch NAME of an emplid as of JOB table's EFFDT', however as stated above, your code will return ALL future names. The simplest solution I found was to use the additional code I provided in Scenario A and add a decode as follows:

      SELECT J.EMPLID, decode(sign(j.effdt-n.effdt),-1,null,n.name) "NAME"
      FROM PS_JOB J, PS_NAMES N
      WHERE
      J.EFFDT = (SELECT MAX(J_ED.EFFDT) FROM PS_JOB J_ED
      WHERE J.EMPLID = J_ED.EMPLID
      AND J.EMPL_RCD = J_ED.EMPL_RCD
      AND J_ED.EFFDT <= SYSDATE)
      AND J.EFFSEQ =(SELECT MAX(J_ES.EFFSEQ) FROM PS_JOB J_ES
      WHERE J.EMPLID = J_ES.EMPLID
      AND J.EMPL_RCD = J_ES.EMPL_RCD
      AND J.EFFDT = J_ES.EFFDT)
      AND J.EMPLID = N.EMPLID (+)
      AND (N.EFFDT IS NULL
      OR ((SELECT MAX(N_ED.EFFDT)
      FROM PS_NAMES N_ED
      WHERE N_ED.NAME_TYPE=N.NAME_TYPE
      AND N_ED.EMPLID = N.EMPLID
      AND N_ED.EFFDT <= J.EFFDT) IS NULL and n.effdt=(select min(n1.effdt) from ps_names n1 where n1.name_type=n.name_type and n1.emplid=n.emplid))
      OR N.EFFDT=(SELECT MAX(N_ED.EFFDT)
      FROM PS_NAMES N_ED
      WHERE N_ED.NAME_TYPE=N.NAME_TYPE
      AND N_ED.EMPLID = N.EMPLID
      AND N_ED.EFFDT <= J.EFFDT))

      Hope this helps anyone who is looking to outer join an effective dated table to another effective dated table and is losing/gaining rows.

      - Ed B.

      ReplyDelete

    Please refrain for marketing messages and unnecessary back links.

    Post Top Ad

    Post Bottom Ad