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

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"

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...