Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 433
  • Last Modified:

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.?
0
hej613
Asked:
hej613
  • 2
  • 2
  • 2
2 Solutions
 
slightwv (䄆 Netminder) Commented:
Select S_HIRINGCLASS.NEXTVAL outside the insert and use

S_HIRINGCLASS.CURRVAL inside the insert.
0
 
hej613Author Commented:
That sounds simple - but how would I select it outside of the insert?

forgive the very basic question..

Thanks
0
 
SharathData EngineerCommented:
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

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
SharathData EngineerCommented:
corrected this if you need.
INSERT INTO HIRINGCLASS (HC_AP_ID, hc_start_date, HC_CLASS)
SELECT AP_ID, SYSDATE,(select coalesce(max(HC_CLASS),0)+1 from HIRINGCLASS) HC_CLASS
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

0
 
slightwv (䄆 Netminder) Commented:
>>That sounds simple - but how would I select it outside of the insert?

select S_HIRINGCLASS.NEXTVAL from dual;

INSERT INTO HIRINGCLASS (HC_AP_ID, hc_start_date, HC_CLASS)
SELECT AP_ID, SYSDATE, S_HIRINGCLASS.CURRVAL
FROM APPLICANT, CIVILSERVICE
...
0
 
hej613Author Commented:
Great Solutions - Both.. Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now