Hello everyone,
I am searching for help on this. I have the following query that only returns one line:
WITH
N_PER_ALL_PEOPLE
AS (
SELECT Person_id, PERSON_NUMBER
FROM (
SELECT PAPF.Person_id, PAPF.PERSON_NUMBER,
ROW_NUMBER() OVER (
PARTITION BY person_id
ORDER BY object_version_number DESC
) AS rn
FROM PER_ALL_PEOPLE_F PAPF
WHERE PAPF.EFFECTIVE_START_DATE <= TO_DATE('31/12/2024','DD/MM/YYYY')
/* Active the line to see results
and PAPF.person_id in (300000002743757, --4790
300000011602733 -- 149913
) */
)
WHERE rn = 1),
N_PER_PERIODS_OF_SERVICE
AS (
SELECT *
FROM (
SELECT P.person_id, P.DATE_START, P.ACTUAL_TERMINATION_DATE, P.period_of_service_id,
ROW_NUMBER() OVER (
PARTITION BY PERSON_ID
ORDER BY ACTUAL_TERMINATION_DATE DESC
) AS RN
FROM PER_PERIODS_OF_SERVICE P
WHERE DATE_START <= to_date('31/12/2024','DD/MM/YYYY')
and P.person_id in (select person_id from N_PER_ALL_PEOPLE)
)
WHERE RN = 1),
N_PER_ALL_ASSIGNMENTS_M
as
(SELECT *
FROM (
SELECT PAAM.person_id,
PAAM.ASSIGNMENT_NAME,
PAAM.ASSIGNMENT_NUMBER,
paam.Employment_category,
PAAM.HOURLY_SALARIED_CODE,
PAAM.FREQUENCY,
PAAM.FULL_PART_TIME,
PAAM.ADJUSTED_FTE,
PAAM.ASS_ATTRIBUTE3,
PAAM.ASS_ATTRIBUTE8,
PAAM.ASS_ATTRIBUTE9,
PAAM.ASS_ATTRIBUTE6,
PAAM.PERMANENT_TEMPORARY_FLAG,
PAAM.ACTION_CODE,
paam.LEGAL_ENTITY_ID,
PAAM.EFFECTIVE_START_DATE,
paam.BUSINESS_UNIT_ID,
PAAM.LABOUR_UNION_MEMBER_FLAG,
PAAM.UNION_ID ,
PAAM.ASS_ATTRIBUTE7,
PAAM.ASS_ATTRIBUTE11,
PAAM.ASS_ATTRIBUTE12,
PAAM.COLLECTIVE_AGREEMENT_ID,
PAAM.PROJECTED_ASSIGNMENT_END,
ROW_NUMBER() OVER (
PARTITION BY PERSON_ID
ORDER BY EFFECTIVE_END_DATE DESC
) AS RN
from PER_ALL_ASSIGNMENTS_M PAAM
where PAAM.EFFECTIVE_START_DATE <= TRUNC(to_date('31/12/2024','DD/MM/YYYY'))
AND PAAM.ASSIGNMENT_TYPE IN ('E','C')
AND PAAM.EFFECTIVE_LATEST_CHANGE='Y'
and PAAM.period_of_service_id in (SELECT period_of_service_id
FROM N_PER_PERIODS_OF_SERVICE NPPOS
where NPPOS.person_id = PAAM.person_id)
)
WHERE RN = 1)
select NPAP.person_number, NPAP.person_id, PPNF.FIRST_NAME, NPASS.ASSIGNMENT_NUMBER
from N_PER_ALL_PEOPLE NPAP,
PER_PERSON_NAMES_F PPNF
,N_PER_ALL_ASSIGNMENTS_M NPASS
where NPAP.person_id = PPNF.person_id
and NPAP.PERSON_NUMBER in ( '149913', '4790')
AND PPNF.NAME_TYPE='GLOBAL'
and TO_DATE('31/12/2024','DD/MM/YYYY') between PPNF.EFFECTIVE_START_DATE and PPNF.EFFECTIVE_END_DATE
and NPASS.person_id = PPNF.person_id
But if I remove the condition in lines 13 to 16, I will have the two expected values:
WithComment:
WithoutComments:
Is there any limitation that I am not aware of?
Thank you to everyone who can help.
Regards
Jaime