[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 431
  • 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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