Difference between the following two querries

[myoracle]

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

 

[jimmyn1]

I think it may have something to do with this part of the query.

and exists (select b.pernr from ads_transfer_assign b
where a.pernr = b.pernr...

and not exists (select c.pernr from ads_transfer_assign c
where a.pernr = c.pernr...

since they are hitting the same tables they are cancelling each other out.