PL/SQL cursor select unique record and print in flat file

I have set of values in a cursor. For example:

CURSOR c_stock_option IS
SELECT empid, '1' AS ISenrolled
FROM employee emp
UNION ALL
SELECT empid, '2' AS ISenrolled
FROM employee emp;

Now I want to check if the empid appears both in first select (where ISenrolled =1) and second select (where ISenrolled =2). I want to only grab the value from first select where enroll=1 and reject the one where enroll=2. I want to only print record开发者_C百科s that qualifies this criteria.

FOR v_stock_option_record IN c_stock_option LOOP
    IF v_esppstock_recs  IN (v_stock_option_record.empid) THEN

    END IF;
    -- Participant file.
    v_member_string_1 := v_stock_option_record.empid || G_DELIMITER || --1. participant id
    v_stock_option_record.last_name || G_DELIMITER || --4. Last Name
    v_stock_option_record.first_name || G_DELIMITER || --5. First Name
END loop;

In the first part of query it is selecting all the employees that have purchased the stocks (that will give only the set of employees who have purchased the stocks, other part of the query gives all the active employees in the company, so the employee who is in the first part of the select will always be in second part of the select, but the employee who is in second part of select is not necessarily in the 1st part. In the scenario when employee appears in both parts what I need to do is just select the employee who has isenrolled=1). Below is the SQL to differentiate

SELECT
    empid,
    '1' AS ISenrolled
    FROM employee emp,
    hrempusf usf
    where emp.employee = usf.employee
          AND usf.field_key = 76 ---- 76 determines that employee has purchased stocks
UNION ALL
    SELECT
     empid,
    '2' AS ISenrolled
     FROM employee emp;

You don't need complicated PL/SQL for this, you just need a LEFT OUTER JOIN. This will return all the EMPLOYEE records, regardless of whether it matches an HREMPUSF record.

SELECT
     empid
     , nvl2(usf.field_key ,'1', '2') AS ISenrolled
  FROM employee emp
     left outer join hrempusf usf
          on ( usf.employee = emp.employee
              and usf.field_key = 76 )

The NVL2() returns the second value if the first argument is not null and the third argument if it is null.

Here is one way that should work, it will only return the 1=>IsEnrolled if it exists, otherwise it'll return the 2 IsEnrolled.

The "data" is to mimic your two select statements.

with data as(
select 1 empId, 1 ISEnrolled from dual
union
select 2 empId, 1 ISEnrolled from dual
union
select 3 empId, 1 ISEnrolled from dual
union
select 4 empId, 1 ISEnrolled from dual
union
select 5 empId, 1 ISEnrolled from dual /** these 5 are mimicing your first select */
union
select 1 empId, 2 ISEnrolled from dual /** the next are the 'all' */
union
select 2 empId, 2 ISEnrolled from dual
union
select 3 empId, 2 ISEnrolled from dual
union
select 4 empId, 2 ISEnrolled from dual
union
select 5 empId, 2 ISEnrolled from dual
union
select 6 empId, 2 ISEnrolled from dual
union
select 7 empId, 2 ISEnrolled from dual
union
select 8 empId, 2 ISEnrolled from dual
union
select 9 empId, 2 ISEnrolled from dual
union
select 10 empId, 2 ISEnrolled from dual)
,
onlyOneIsEnrolled as (
  select empId, isEnrolled
    from data
    where isEnrolled = 1
) ,
notInOneIsEnrolled as(
  select empId, isEnrolled
    from data d
    where not exists(select null
                           from onlyOneIsEnrolled ooie
                            where ooie.empid = d.empId
                            )
)
select EmpId, isEnrolled
  from onlyOneIsEnrolled
  union
select EmpId, isEnrolled
  from notInOneIsEnrolled
order by isEnrolled, EmpId

This is just one way to accomplish the task, the onlyOneIsEnrolled gathers all the 1's, then notInOneIsEnrolled gets all the emps not in the above, the final part of the query puts them together.

There are many ways to accomplish this task, this utilizes CTE's but depending on your need you may be able to do this without utilizing the with clause.