IF i have to compare two cursors and retrun unique vals how do i do that
example CURSOR c_stock_option IS Select empid, name, ssn, isenrolled from employee where isenrolled=1
CURSOR c_espp_option IS Selec开发者_如何转开发t empid, name, ssn, isenrolled from employee where isenrolled=2
Now i want to reject all the recs in second cursors that are in cursor 1 select, how do i do that
Ummm.....by definition, all of the rows where isenrolled=2 does not overlap with the rows where isenrolled=1. But I think you're asking a more general question about how to exclude rows from one result set that are in another.
If this is the case, you could take a few different approaches:
1)
CURSOR c_stock_option IS
Select empid, name, ssn, isenrolled from employee where isenrolled=1
MINUS
Select empid, name, ssn, isenrolled from employee where isenrolled=2
2)
CURSOR c_stock_option IS
Select empid, name, ssn, isenrolled from employee
where isenrolled=1
and empid not in (
Select empid, name, ssn, isenrolled from employee where isenrolled=2)
3)
CURSOR c_stock_option IS
Select empid, name, ssn, isenrolled from employee e
where isenrolled=1
and not exists(
Select 1 from employee where e.empid = employee.empid and isenrolled=2)
Which you choose depends on your situation, data model, indexing etc.
精彩评论