×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Difference between the following two querries

Difference between the following two querries

Difference between the following two querries

(OP)
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

RE: Difference between the following two querries

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close