Results 1 to 3 of 3

Thread: Full outer join

  1. #1
    Join Date
    Nov 2010
    Posts
    3

    Unanswered: Full outer join

    This is my query below. I have 2 derived tables. One called Pay and the other called earnings1.
    I used a full outer join to bring back all rows for an employee. However I cannot make it work. Can someone please help me? thanks
    WHEN I put a "Where " Clause in this statements using earnings.PER_ID or pay.PER_ID, I receive the pay records "PAY" or the earnings records "earnings1" but not both!





    --------------------------
    SELECT DISTINCT
    earnings1.PER_ID,
    earnings1.earn_month,
    earnings1.earn_amt,
    earnings1.EARN_FISCAL_YEAR,
    earnings1.earn_CONTRACT_YEAR,
    pay.pay_month,
    V_TTPS_RECORD__PAY_AMT


    FROM

    (SELECT
    P.PER_ID,
    earn_month,
    earn_amt,
    EARN_FISCAL_YEAR,
    earn_CONTRACT_YEAR
    FROM
    PERSON P LEFT JOIN
    (
    SELECT
    earn.PER_ID,
    ACCT_NUM_ELM_03 acc_object,
    EARN_RUN_PRFL_ID,
    ea.earn_CONTRACT_YEAR,
    EARN_FISCAL_YEAR,
    PAYROLL_OPRTN_TYP_CD earnings_Description,
    sum(EARN_JRNL_GL_ACCT_AMT) earn_amt,
    EARN_JRNL_BEGIN_DT,
    EARN_JRNL_END_DT,
    EARN_MONTH_NUM,
    EARN_MONTH

    FROM
    V_WFISD_EARNING earn LEFT JOIN(SELECT


    PER_ID,
    POS_CONTROL_NUM,
    ea.EMP_ASSGN_DT,
    ea.EMP_ASSGN_END_DT,
    (CASE WHEN (YEAR(ea.EMP_ASSGN_DT) IN ('2009') or YEAR(ea.EMP_ASSGN_DT) IN ('2010')) and YEAR(ea.EMP_ASSGN_END_DT) IN ('2009','2010') then '2010'
    WHEN (YEAR(ea.EMP_ASSGN_DT) IN ('2010') or YEAR(ea.EMP_ASSGN_DT) IN ('2011')) and YEAR(ea.EMP_ASSGN_END_DT) IN ('2010','2011') then '2011' end)earn_CONTRACT_YEAR

    FROM EMPLOYEE_ASSIGNMENT EA
    ) EA ON EA.PER_ID = EARN.PER_ID
    and
    EA.EMP_ASSGN_DT = earn.EMP_ASSGN_DT
    --'700005046'
    GROUP BY
    earn.PER_ID,
    ea.earn_contract_year,
    earn.PER_ID,
    EARN_MONTH,
    PAYROLL_OPRTN_TYP_CD ,
    EARN_FISCAL_YEAR,
    ROLLUP((



    EARN_RUN_PRFL_ID,
    ACCT_NUM_ELM_03,

    earn.EMP_ASSGN_DT,
    ea.EMP_ASSGN_END_DT,
    EARN_JRNL_BEGIN_DT,
    EARN_JRNL_END_DT,
    EARN_MONTH_NUM,

    EARN_JRNL_GL_ACCT_AMT))
    HAVING GROUPING (EARN_JRNL_GL_ACCT_AMT) =1
    )EARNINGS
    on
    earnings.PER_ID = P.PER_ID
    )earnings1




    FULL OUTER JOIN



    (SELECT P.PER_ID,
    pay_CONTRACT_YEAR,
    payrecords.PAY_MONTH,
    V_TTPS_RECORD__PAY_AMT,
    V_TTPS_FISCAL_YR
    FROM
    PERSON P LEFT JOIN
    (
    SELECT
    P.PER_ID,
    PAY.FIRST_NAME,
    PAY.LAST_NAME,
    Pay_month,
    PAY.description,
    V_TTPS_FISCAL_YR,

    V_TTPS_CHECK_DATE,
    V_TPPS_PAYROLL_PROFILE_ID,
    V_TTPS_PAYCHECK_PROFILE_ID,
    sum(V_TTPS_RECORD__PAY_AMT)V_TTPS_RECORD__PAY_AMT,
    pay_CONTRACT_YEAR

    FROM (SELECT DISTINCT
    PER_ID,

    PAYROLL_OPRTN_TYP_CD Description,
    EARN_MONTH monthofYear

    fROM V_WFISD_EARNING)MONTHS LEFT JOIN
    (
    (SELECT DISTINCT
    V_TTPS_EMPLOYEE_ID PER_ID ,
    V_TTPS_FISCAL_YR,
    V_TTPS_EMPLOYEE_FIRST_NAME FIRST_NAME ,
    V_TTPS_EMPLOYEE_LAST_NAME LAST_NAME,
    date(V_TTPS_CHECK_DATE)V_TTPS_CHECK_DATE,

    UPPER(MONTHNAME(SUMM.V_TTPS_PAY_PERIOD_END_DATE)) Pay_month,

    V_TPPS_PAYROLL_PROFILE_ID,
    V_TTPS_PAYCHECK_PROFILE_ID,
    V_TTPS_RECORD__PAY_AMT,

    V_TTPS_PAY_PERIOD_END_DATE,
    (CASE WHEN V_TTPS_RECORD_TYPE = 'Employee Assignment Stipend Pay' Then 'Employee Assignment Stipend' else V_TTPS_RECORD_TYPE end)Description ,
    (CASE WHEN YEAR(ea.EMP_ASSGN_DT) IN ('2009') or YEAR(ea.EMP_ASSGN_END_DT) IN ('2009','2010') then '2010'
    WHEN YEAR(ea.EMP_ASSGN_DT) IN ('2010') or YEAR(ea.EMP_ASSGN_END_DT) IN ('2010','2011') then '2011' end) Pay_contract_year
    FROM
    V_TEAMS_PAYROLL_PAYCHECK_SUMMARY SUMM LEFT JOIN EMPLOYEE_ASSIGNMENT EA ON EA.PER_ID = SUMM.V_TTPS_EMPLOYEE_ID
    WHERE
    days(V_TTPS_PAY_PERIOD_END_DATE) between days(ea.emp_assgn_dt)+20 and days(ea.EMP_ASSIGN_LAST_CHECK_DT) + 10
    and
    V_TTPS_ACCT_E_03 ='2160'

    )
    ) PAY

    ON months.PER_ID = PAY.PER_ID
    and
    months.monthofYear = Pay.PAY_MONTH
    and
    months.description = Pay.description
    LEFT JOIN PERSON P ON P.PER_ID = pay.PER_ID
    where
    pay.PER_ID = p.PER_ID --'700005046'
    GROUP BY
    P.PER_ID,
    PAY.PER_ID,
    PAY.FIRST_NAME,
    PAY.LAST_NAME,
    Pay_month,
    PAY.description,
    V_TTPS_CHECK_DATE,
    V_TTPS_FISCAL_YR,
    V_TPPS_PAYROLL_PROFILE_ID,
    V_TTPS_PAYCHECK_PROFILE_ID,
    Pay_contract_year,
    ROLLUP((
    V_TTPS_RECORD__PAY_AMT
    ))
    HAVING GROUPING (V_TTPS_RECORD__PAY_AMT) = 1)payrecords
    ON payrecords.PER_ID = P.PER_ID) pay


    on pay.PER_ID = earnings1.PER_ID
    and
    pay.PAY_MONTH = earnings1.earn_month
    and
    pay.pay_CONTRACT_YEAR = earnings1.earn_contract_year

    and
    pay.V_TTPS_FISCAL_YR = earnings1.EARN_FISCAL_YEAR

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Do you have the query also with some formatting and indentation so that one can easily see what happens where?

    I believe that your predicates on the outer select do not consider the case that columns derived from the subselects PAY and EARNINGS1 will actually be NULL in an outer-join case. So if there was no row in PAY for a specific PER_ID, the predicate "pay.per_id = earnings1.per_id" gives "unknown = something", which evaluates to "unknown" which is interpreted as "false", which leads to the row being discarded. You'd have to write something like "pay.per_id = earnings1.per_id OR pay.per_id IS NULL OR earnings1.per_id IS NULL"
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    WHEN I put a "Where " Clause in this statements using earnings.PER_ID or pay.PER_ID,
    I receive the pay records "PAY" or the earnings records "earnings1" but not both!
    Please try to apply the conditions for COALESCE(earnings.PER_ID , pay.PER_ID).


    And, you may want to specify the select list like this...
    Code:
    SELECT DISTINCT
           COALESCE(earnings1.PER_ID             , pay.PER_ID)            AS PER_ID
         , COALESCE(earnings1.earn_month         , pay.pay_month)         AS month
         , COALESCE(earnings1.EARN_FISCAL_YEAR   , pay.V_TTPS_FISCAL_YR)  AS FISCAL_YEAR
         , COALESCE(earnings1.earn_CONTRACT_YEAR , pay.pay_CONTRACT_YEAR) AS CONTRACT_YEAR
         , earnings1.earn_amt
         , V_TTPS_RECORD__PAY_AMT
      FROM 
    ...
    ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •