troubleshooting Question

Get one Sequence Value for entire statment

Avatar of hej613
hej613 asked on
Oracle Database
6 Comments2 Solutions453 ViewsLast Modified:
Afternoon Experts - I have a question on doing an insert with a sequence.

As an example, I have a table of users, table for test grades and a table to house my hiring class.  

Applicant Table:
AP__ID
AP_NAME
AP_IsHired

Civil Service Grades:
CS_ID,
CS_APID (fk over to AP_ID)
CS_GRADE,
CS_TEST_DATE,
CS_EXPIRE_DATE

HIRING CLASS:
HC_ID
HC_APID (fk to AP_ID)
HC_STARTDATE,
HC_ENDDATE
HC_CLASS

I wrote a quick sequence to get the next available hiring class

CREATE SEQUENCE S_HIRINGCLASS
  MINVALUE 1
  MAXVALUE 99999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

When I run this it "works" But it enters each person with a new Sequence


INSERT INTO HIRINGCLASS (HC_AP_ID, hc_start_date, HC_CLASS)
SELECT AP_ID, SYSDATE, S_HIRINGCLASS.NEXTVAL
FROM APPLICANT, CIVILSERVICE
WHERE NOT EXISTS
(SELECT 1
FROM HIRINGCLASS
WHERE APPLICANT.AP_ID = HIRINGCLASS.HC_AP_ID)
AND CS_APPID = AP_ID
AND AP_ISHIRED = 'N'
AND ROUND (F_MONTH_DIFF (SYSDATE, CS_EXP_DATE)) > 3;

Each time I run this I want to get ALL applicants that qualify (AP_ISHIRED is "N" and there are more than 3 months till the expire date on the scores) and assign them all to ONE hiring class, then the next time I run this statement it would pick the nextval from my sequence and assign them to the following class, etc, etc.?
ASKER CERTIFIED SOLUTION
Sharath S
Data Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros