Hi,
I have a querry in my procedure as follows. The functionality of the following querry is to select personnel number, organization code and on_date from a table.
It should select only those pernsonnel_numbers(pernr) which exists with previous date also but that personnel_number (pernr) should have a different organization code(org_unit_cd) on previous date.
It's not selecting any records when it's written as below.
--------------------------------------------------------------------------------------------------
select a.org_unit_cd, a.pernr, a.on_date from ads_transfer_assign a
where to_char(a.on_date,'DD-MON-YYYY') = to_char(transfer_dt,'DD-MON-YYYY')
and exists (select b.pernr from ads_transfer_assign b
where a.pernr = b.pernr
and to_char(b.on_date,'DD-MON-YYYY') = to_char(transfer_dt-1,'DD-MON-YYYY') )
and not exists (select c.pernr from ads_transfer_assign c
where a.pernr = c.pernr
and a.org_unit_cd = c.org_unit_cd
and to_char(c.on_date,'DD-MON-YYYY') = to_char(transfer_dt-1,'DD-MON-YYYY' ))
---------------------------------------------------------------------------------------------------
But it's selecting records when it is written as below.
select a.org_unit_cd, a.pernr, a.on_date from hrdw.ads_transfer_assign a
where to_char(a.on_date,'DD-MON-YYYY') = to_char(last_day(add_months(sysdate,-2)),'DD-MON-YYYY')
and exists
(select b.pernr from hrdw.ads_transfer_assign b
where to_char(b.on_date,'DD-MON-YYYY') = to_char(last_day(add_months(sysdate,-2))-1,'DD-MON-YYYY')
and a.pernr = b.pernr
and a.org_unit_cd <> b.org_unit_cd
)
Is there any difference between the above two?
Can any body help us?
Thanks,
Yanumula
I have a querry in my procedure as follows. The functionality of the following querry is to select personnel number, organization code and on_date from a table.
It should select only those pernsonnel_numbers(pernr) which exists with previous date also but that personnel_number (pernr) should have a different organization code(org_unit_cd) on previous date.
It's not selecting any records when it's written as below.
--------------------------------------------------------------------------------------------------
select a.org_unit_cd, a.pernr, a.on_date from ads_transfer_assign a
where to_char(a.on_date,'DD-MON-YYYY') = to_char(transfer_dt,'DD-MON-YYYY')
and exists (select b.pernr from ads_transfer_assign b
where a.pernr = b.pernr
and to_char(b.on_date,'DD-MON-YYYY') = to_char(transfer_dt-1,'DD-MON-YYYY') )
and not exists (select c.pernr from ads_transfer_assign c
where a.pernr = c.pernr
and a.org_unit_cd = c.org_unit_cd
and to_char(c.on_date,'DD-MON-YYYY') = to_char(transfer_dt-1,'DD-MON-YYYY' ))
---------------------------------------------------------------------------------------------------
But it's selecting records when it is written as below.
select a.org_unit_cd, a.pernr, a.on_date from hrdw.ads_transfer_assign a
where to_char(a.on_date,'DD-MON-YYYY') = to_char(last_day(add_months(sysdate,-2)),'DD-MON-YYYY')
and exists
(select b.pernr from hrdw.ads_transfer_assign b
where to_char(b.on_date,'DD-MON-YYYY') = to_char(last_day(add_months(sysdate,-2))-1,'DD-MON-YYYY')
and a.pernr = b.pernr
and a.org_unit_cd <> b.org_unit_cd
)
Is there any difference between the above two?
Can any body help us?
Thanks,
Yanumula