开发者

cursor- how to compare and select unique records

开发者 https://www.devze.com 2023-01-28 02:03 出处:网络
IF i have to compare two cursors and retrun unique vals how do i do that example CURSOR c_stock_option IS

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.

0

精彩评论

暂无评论...
验证码 换一张
取 消