Left Join Not Giving The Null Values In The Table Sql

--Changed the query to new syntax hence posting as new----- I have created a query to retrieve employee information. There may be employees that do not have information in pay_payr

Solution 1:

There are few issues which I have listed down as following

1. You have used this as INNER JOIN as following but ON condition consists of the column from a table: PAY_PAYROLL_ACTIONS which will make the condition false if there is no data of employee in PAY_PAYROLL_ACTIONS table.

   JOIN PAY_TIME_PERIODS PTP ON ( PPA.EARN_TIME_PERIOD_ID = PTP.TIME_PERIOD_ID )

2. You have used following 5 WHERE condition.

   • PPA.ACTION_TYPE IS NULL
   • TO_CHAR(NVL(PPA.EFFECTIVE_DATE, '2019-09-01'), 'YYYY-MM-DD') BETWEEN ( '2019-09-01' ) AND ( '2019-12-01' )
   • PPA.CONSOLIDATION_SET_ID = PCS.CONSOLIDATION_SET_ID
   • NVL(PPA.EFFECTIVE_DATE, TRUNC(SYSDATE)) BETWEEN PAYREL.START_DATE AND PAYREL.END_DATE
   • NVL(PPA.EFFECTIVE_DATE, TRUNC(SYSDATE)) BETWEEN PAPD.START_DATE AND PAPD.END_DATE

These conditions also affect the final result based on the presence of the data in table PAY_PAYROLL_ACTIONS. I am trying to give you the solution(see inline comments for a description of change) according to my knowledge of these tables as follows:

SELECTDISTINCT
    PAPF.PERSON_NUMBER,
    NAME.FIRST_NAME,
    NAME.LAST_NAME,
    PAAM.ASSIGNMENT_NUMBER,
    PY.PAYROLL_NAME,
    (
        SELECT
            MEANING
        FROM
            FND_LOOKUP_VALUES
        WHERE
            LOOKUP_TYPE ='ACTION_TYPE'AND LOOKUP_CODE = PPA.ACTION_TYPE
            ANDLANGUAGE='US'AND ENABLED_FLAG ='Y'
    ) ACTION_TYPE,
    TO_CHAR(PPA.EFFECTIVE_DATE, 'DD-MON-YYYY') EFFECTIVE_DATE,
    PPA.PAYROLL_ACTION_ID   ACTION_NUMBER,
    PCS.CONSOLIDATION_SET_NAME,
    TO_CHAR(FINC, 'DD-MON-YYYY') FINC,
    PASV.USER_STATUS
FROM
    PAY_ASSIGNED_PAYROLLS_DN PAPD
    JOIN PAY_PAYROLL_TERMS PT ON ( PAPD.PAYROLL_TERM_ID = PT.PAYROLL_TERM_ID )
    JOIN PAY_PAY_RELATIONSHIPS_DN PR ON ( PT.PAYROLL_RELATIONSHIP_ID = PR.PAYROLL_RELATIONSHIP_ID )
    JOIN PAY_ALL_PAYROLLS_F PY ON ( PAPD.PAYROLL_ID = PY.PAYROLL_ID )
    JOIN PER_ALL_PEOPLE_F PAPF ON ( PR.PERSON_ID = PAPF.PERSON_ID )
    JOIN PAY_CONSOLIDATION_SETS PCS ON ( PCS.CONSOLIDATION_SET_ID = PY.CONSOLIDATION_SET_ID )
    JOIN PER_PERSON_NAMES_F NAME ON ( PAPF.PERSON_ID = NAME.PERSON_ID )
    JOIN PAY_REL_GROUPS_DN PAYREL ON ( PAYREL.PARENT_REL_GROUP_ID = PAPD.PAYROLL_TERM_ID )
    JOIN PER_ALL_ASSIGNMENTS_M PAAM ON ( PAYREL.ASSIGNMENT_ID = PAAM.ASSIGNMENT_ID )
    JOIN PAY_PAYROLL_REL_ACTIONS PPRA ON ( PPRA.PAYROLL_RELATIONSHIP_ID = PR.PAYROLL_RELATIONSHIP_ID )
    JOIN PER_ASSIGNMENT_STATUS_TYPES_VL PASV ON ( PASV.ASSIGNMENT_STATUS_TYPE_ID = PAAM.ASSIGNMENT_STATUS_TYPE_ID )
    JOIN PER_LEGISLATIVE_DATA_GROUPS_VL LDG ON ( LDG.LEGISLATION_CODE = PAAM.LEGISLATION_CODE )
    LEFTJOIN PAY_PAYROLL_ACTIONS PPA ON ( PPA.PAYROLL_ID = PY.PAYROLL_ID
                                           AND PPA.PAYROLL_ACTION_ID = PPRA.PAYROLL_ACTION_ID 
                                           -- TEJASH : SUGGESTION-1 : ADDED FOLLOWING CONDITIONS IN JOIN CONDITIONS INSTEAD OF WHERE CLAUSEAND PPA.ACTION_TYPE ISNULL-- CHANGED FOLLOWING CONDITION TO USE DATES RATHER THAN CHARAND NVL(PPA.EFFECTIVE_DATE, DATE'2019-09-01') BETWEENDATE'2019-09-01'ANDDATE'2019-12-01'AND PPA.CONSOLIDATION_SET_ID = PCS.CONSOLIDATION_SET_ID
                                           AND NVL(PPA.EFFECTIVE_DATE, TRUNC(SYSDATE)) BETWEEN PAYREL.START_DATE AND PAYREL.END_DATE
                                           AND NVL(PPA.EFFECTIVE_DATE, TRUNC(SYSDATE)) BETWEEN PAPD.START_DATE AND PAPD.END_DATE )
    -- TEJASH : SUGGESTION-2 : USE LEFT JOIN HERELEFTJOIN PAY_TIME_PERIODS PTP ON ( PPA.EARN_TIME_PERIOD_ID = PTP.TIME_PERIOD_ID )
WHERE1=1AND PTP.PAYROLL_ID = PY.PAYROLL_ID
    AND PAAM.ASSIGNMENT_TYPE ='E'--ADDED condition---    AND PASV.USER_STATUS LIKE'%Payroll Eligible'AND ( FINC > PTP.START_DATE
          OR FINC ISNULL )
-- COMMENTED BY TEJASH--    AND ( PPA.ACTION_TYPE IS NULL --              OR ppa.action_type IN ( 'Q', 'R' )--     ) --  AND ldg.name = Nvl(:P_LEGISLATIVE_GROUP, ldg.name)  --AND pasv.user_status = Nvl(:P_ASSIGNMENT_STATUS,  --                     pasv.user_status)  -- COMMENTED BY TEJASH--AND TO_CHAR(NVL(PPA.EFFECTIVE_DATE, '2019-09-01'), 'YYYY-MM-DD') BETWEEN ( '2019-09-01' ) AND ( '2019-12-01' ) -------    --AND PPA.CONSOLIDATION_SET_ID = PCS.CONSOLIDATION_SET_IDAND PAYREL.GROUP_TYPE ='A'AND NAME.NAME_TYPE ='GLOBAL'--AND NVL(PPA.EFFECTIVE_DATE, TRUNC(SYSDATE)) BETWEEN PAYREL.START_DATE AND PAYREL.END_DATE--AND NVL(PPA.EFFECTIVE_DATE, TRUNC(SYSDATE)) BETWEEN PAPD.START_DATE AND PAPD.END_DATEAND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
    AND TRUNC(SYSDATE) BETWEEN PY.EFFECTIVE_START_DATE AND PY.EFFECTIVE_END_DATE
    AND TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
    AND TRUNC(SYSDATE) BETWEEN NAME.EFFECTIVE_START_DATE AND NAME.EFFECTIVE_END_DATE

Cheers!!

Solution 2:

There are many WHERE clauses, which depend on the right side of the LEFT JOIN table. Whenever there is no matching value in the right side table, you will be having NULL values.

you are having LEFT JOIN as below

left join pay_payroll_actions ppa 
              ON ( ppa.payroll_id = py.payroll_id 
                   AND PPA.payroll_action_id = PPRA.payroll_action_id ) 

Post this LEFT JOIN, you are having lots of WHERE conditions on the ppa as given below:

AND To_char(Nvl(ppa.effective_date, '2019-09-01'), 'YYYY-MM-DD') BETWEEN 
           ( 
           '2019-09-01' ) AND ( '2019-12-01' ) 
       -------    AND ppa.consolidation_set_id = pcs.consolidation_set_id 
 AND Nvl(ppa.effective_date, Trunc(SYSDATE)) BETWEEN 
           payrel.start_date AND payrel.end_date 
       AND Nvl(ppa.effective_date, Trunc(SYSDATE)) BETWEEN 
           papd.start_date AND papd.end_date 

Due to these WHERE conditions, many of the rows are getting filtered. Ideally, your where clauses should depend on the LEFT side of the JOIN, as they will be non-null values.