NULL尽管存在值，但仍会产生值

将查询更改为新建议，并遇到另一个问题，因此以NEW POST的形式发布

我创建了以下查询，为表中不存在薪资操作类型PAY_PAYROLL_ACTIONS 或action_type为'Q'，'R'的那些雇员提供结果。

现在，下面的查询返回一个特定雇员的值，但同时返回null和'Q'，'R'。现在，如果员工在某个特定日期的值为“ Q”或“ R”，那么它又如何返回null？

在此处输入图片说明

我需要在下面的查询中添加一个条件，以便如果某个员工有ACTION_TYPE_code“ Q”，“ R”，则它不会变为NULL条件。

即对于上述雇员，由于查询已获取action_type不为636，因此不应该返回NULL action_type ...

仅当ACTION_TYPE IS NULL在那个特定日期且没有其他值时才应返回

select * from (SELECT DISTINCT PAPF.PERSON_NUMBER, NAME.FIRST_NAME, NAME.LAST_NAME, PAAM.ASSIGNMENT_NUMBER, PY.PAYROLL_NAME, PPA.ACTION_TYPE ACTION_TYPE_code, ( SELECT MEANING FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE = 'ACTION_TYPE' AND LOOKUP_CODE = PPA.ACTION_TYPE AND LANGUAGE = '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 ) LEFT JOIN 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 CLAUSE -- AND PPA.ACTION_TYPE IS NULL -- CHANGED FOLLOWING CONDITION TO USE DATES RATHER THAN CHAR AND NVL(PPA.EFFECTIVE_DATE, DATE '2019-09-01') BETWEEN DATE '2019-09-01' AND DATE '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 HERE LEFT JOIN PAY_TIME_PERIODS PTP ON ( PPA.EARN_TIME_PERIOD_ID = PTP.TIME_PERIOD_ID AND PTP.PAYROLL_ID = PY.PAYROLL_ID ) WHERE 1 = 1

AND PAAM.ASSIGNMENT_TYPE = 'E' 
   --ADDED condition---    
AND PASV.USER_STATUS LIKE '%Payroll Eligible'
AND ( FINC > PTP.START_DATE
      OR FINC IS NULL )

-- 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_ID AND 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_DATE AND 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 ) where (ACTION_TYPE_code is null or ACTION_TYPE_code in ('Q','R'))