问题描述
-将查询更改为新语法,因此发布为new -----
--Changed the query to new syntax hence posting as new-----
我创建了一个查询以检索员工信息.可能有一些员工在pay_payroll_actions表中没有信息.为此,我在pay_payroll_actions表上使用了左联接,但它没有向我提供在此表中没有信息的员工的输出.
I have created a query to retrieve employee information. There may be employees that do not have information in pay_payroll_actions table. For that, I have used the left join on the pay_payroll_actions table but it is not giving me output with employees that do not have info in this table.
有人可以帮助我解决这个问题吗?
Can someone pls help me with this query as to how can this be corrected?
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'))
推荐答案
以下列出的几个问题
-
您已按以下方式将其用作
INNER JOIN
,但ON
条件由表中的列组成:PAY_PAYROLL_ACTIONS
如果PAY_PAYROLL_ACTIONS
表中没有雇员的数据,则该条件将为false
You have used this as
INNER JOIN
as following butON
condition consists of the column from a table:PAY_PAYROLL_ACTIONS
which will make the condition false if there is no data of employee inPAY_PAYROLL_ACTIONS
table.
JOIN PAY_TIME_PERIODS PTP ON ( PPA.EARN_TIME_PERIOD_ID = PTP.TIME_PERIOD_ID )
您已经使用了以下5个WHERE条件.
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
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
基于表PAY_PAYROLL_ACTIONS
中数据的存在,这些条件也会影响最终结果.我正根据我对这些表格的了解,尝试为您提供解决方案(有关更改的说明,请参见内联注释):
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:
SELECT DISTINCT
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
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 )
WHERE
1 = 1
AND 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 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
干杯!
这篇关于左联接不给表sql中的空值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!