asked on

Get one Sequence Value for entire statment

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.?

slightwv (䄆 Netminder)

Select S_HIRINGCLASS.NEXTVAL outside the insert and use

S_HIRINGCLASS.CURRVAL inside the insert.

hej613

ASKER

That sounds simple - but how would I select it outside of the insert?

forgive the very basic question..

Thanks

Sharath S

I think you don't need a sequence here. All you need the max(HC_ID)+1 from the table for every load.
I would suggest to create HC_ID as an integer in your table and assin value like this. See if this helps.

INSERT INTO HIRINGCLASS (HC_ID,HC_AP_ID, hc_start_date, HC_CLASS)
SELECT (select coalesce(max(HC_ID),0)+1 from HIRINGCLASS) HC_ID
      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;

Open in new window

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION