Get one Sequence Value for entire statment
Posted on 2013-05-15
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.
Civil Service Grades:
CS_APID (fk over to AP_ID)
HC_APID (fk to AP_ID)
I wrote a quick sequence to get the next available hiring class
CREATE SEQUENCE S_HIRINGCLASS
START WITH 1
INCREMENT BY 1
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
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.?