Home » SQL & PL/SQL » SQL & PL/SQL » Question on checking for duplicate values in a PL/SQL procedure
Question on checking for duplicate values in a PL/SQL procedure [message #189428] Thu, 24 August 2006 09:59 Go to next message
sbattisti
Messages: 39
Registered: June 2005
Member
Hi, a bit of a PL/SQL n00b here, being forced into duty. Cool I'm trying to write a PL/SQL procedure, and the logic I need to use is a little beyond my limited programming and SQL capability. The code is related to a recruiting system. I have two tables, let's call them INTEREST and ACTIONS.

The INTEREST table has lists of job requisitions a job candidate is interested in. So, one candidate could have multiple rows in this table, and one requisition could have multiple candidates. The two fields I'm interested in are CAND_ID (the candidate unique ID), and REQ_ID (the requisition unique ID).

The ACTIONS table has lists of specific actions recruiters have taken regarding a particular combination of candidates and requisitions. In this table, too, the two fields I'm interested in are CAND_ID (the candidate unique ID), and REQ_ID (the requisition unique ID), along with an ACTION_ID field that identifies the action that occurred.

What I need to do is this:

Write each unique combination of CAND_ID and REQ_ID from INTEREST to a text file. (this is no problem)

THEN, I also need to check the ACTIONS table for any unique combination of CAND_ID and REQ_ID when ACTION=1, and insert a row for these into the same text file, but only if I haven't already inserted a row with the same CAND_ID and REQ_ID.

That last part has me baffled. At the heart of it, I can't figure out a way to check whether I have already inserted a row into the text file with those same values. Any suggestions for approaches? (Please bear in mind my relatively low level of skill. Smile)

Thanks!

Steve


[Updated on: Thu, 24 August 2006 10:01]

Report message to a moderator

Re: Question on checking for duplicate values in a PL/SQL procedure [message #189435 is a reply to message #189428] Thu, 24 August 2006 10:20 Go to previous messageGo to next message
rigatonip
Messages: 50
Registered: December 2005
Member
I'm assuming you've got a cursor in the PL/SQL procedure that's returning the records you need. It might be easiest to simply change what that cursor returns. Something like:

select cand_id, req_id, null as action_id
from interests
union
select cand_id, req_id, action_id
from actions
where (cand_id, req_id) not in (select cand_id, req_id
from interests);

I've added a "dummy" column of action_id to the 1st query that simply returns a null value. If you don't need the action_id from the interests table, then you could take that column out of both queries.

hth
-- pr
Re: Question on checking for duplicate values in a PL/SQL procedure [message #189436 is a reply to message #189428] Thu, 24 August 2006 10:24 Go to previous messageGo to next message
sbattisti
Messages: 39
Registered: June 2005
Member
This is great, thanks!

Regarding this line:

where (cand_id, req_id) not in (select cand_id, req_id
from interests);

Does this code actually match up the combination of those two values? So, in English, "Where the combination of cand_id and req_id does not have a row with the same combination in interests"? If so, COOL! I never knew you could do that. Smile
Re: Question on checking for duplicate values in a PL/SQL procedure [message #189460 is a reply to message #189435] Thu, 24 August 2006 12:54 Go to previous message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
That's actually overkill.
select cand_id,
       req_id
from interests
union
select cand_id,
       req_id, 
from actions
where action_id = 1;

See below to see how the UNION by itself does all of the work for you.
SQL> SELECT *
  2  FROM t;

         A
----------
         1
         2
         3
         3
         3
         4

6 rows selected

SQL> SELECT *
  2  FROM t
  3  UNION
  4  SELECT *
  5  FROM t;

         A
----------
         1
         2
         3
         4

SQL> SELECT *
  2  FROM t
  3  UNION ALL
  4  SELECT *
  5  FROM t;

         A
----------
         1
         2
         3
         3
         3
         4
         1
         2
         3
         3
         3
         4

12 rows selected
Previous Topic: regarding sql query
Next Topic: summary count question
Goto Forum:
  


Current Time: Fri Dec 09 11:38:02 CST 2016

Total time taken to generate the page: 0.10551 seconds