Home » SQL & PL/SQL » SQL & PL/SQL » Code Help: Inserting Records based on records in another table meeting certain parameters (Oracle SQL Developer)
Code Help: Inserting Records based on records in another table meeting certain parameters [message #664869] Wed, 09 August 2017 08:29 Go to next message
cem616
Messages: 3
Registered: August 2017
Junior Member
I've never scripted before and I have been tasked at work to come up with a script for the following:

We have a table "WRCF_Day". The purpose of this table is to list every single day between DATE_HIRED and DATE_END for all mechanics. So, Mech1 was hired on 1/1/2017 and left on 5/7/2017, it would have an individual record for each date between those two dates. This, and a few other additions can let us see who is scheduled to work days, regardless if they entered in time or not in other unrelated tables. The list of all Mechs, active and inactive, with the DATE_HIRED and DATE_END fields, is in table "SD_CF."

We need to delete records within the last 30 day and then insert in updated records, so we can account for changes, such as employees getting hired or leaving within that time period.

I already scripted to delete records for the last 30 days, going off of "DATE_ASSIGNED" which is the field name for the dates. To insert records, my supervisor and I scripted this:
DECLARE 
    CURSOR c_cf IS SELECT b.CF_ID FROM AFM_PHILADELPHIA.SD_CF b
                   WHERE b.DATE_END > trunc(sysdate-30) OR b.DATE_END is null;
    r_cf c_cf%ROWTYPE;
BEGIN
    FOR i in 1..30 LOOP
        OPEN c_cf;
        LOOP
            FETCH c_cf INTO r_cf;
            EXIT WHEN c_cf%NOTFOUND;
            INSERT INTO AFM_PHILADELPHIA.SD_WRCF_DAY a (CF_ID, DATE_ASSIGNED) 
VALUES (r_cf.CF_ID, sysdate-i);
        END LOOP;
        CLOSE c_cf; 
    END LOOP;
END;
I get the result of "anonymous block completed" and no records have been inserted into the WRCF_DAY table.

If we don't consistently reinsert last 30 days, the table won't get updated if someone leaves within that time period and will keep attributing them to be available to work, even if they have left, or won't include new hires.

Example: Mech 1 is hired 1/1/17 and the table shows every single date for him between 1/1/17 and the current date. Mech1 actually leaves 6/30/17. This will get updated in "SD_CF" under DATE_END. However, "WRCF_DAY" will keep generating a new record for each date because it hasn't been updated with his new DATE_END. So before delete, the table shows Mech 1 records between 1/1/17 and 7/13/17, the last 30 get deleted, and then we want to reinsert in records where the DATE_END is greater than those last 30 days, so it will reflect the fact that he left and only show records between 1/1/7 and 6/30/17.

People are constantly coming and going and we need to update the table accordingly, and there's some 400 mechanics at a time usually, so the script needs to run through each mechanic in "SD_CF" determine is their DATE_HIRED or DATE_END meets our parameters, and then insert the appropriate number of records into "WRCF_Day."

I hope that makes sense. Any help would be great! If there is a better script to insert records based off parameters in another table, I am open to other options.
Re: Code Help: Inserting Records based on records in another table meeting certain parameters [message #664870 is a reply to message #664869] Wed, 09 August 2017 08:36 Go to previous messageGo to next message
BlackSwan
Messages: 25639
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Do NOT do in PL/SQL that which can be done in plain SQL.
INSERT INTO .... SELECT ....
Re: Code Help: Inserting Records based on records in another table meeting certain parameters [message #664871 is a reply to message #664870] Wed, 09 August 2017 08:39 Go to previous messageGo to next message
cem616
Messages: 3
Registered: August 2017
Junior Member
I don't know Oracle, but have been tasked with finding a scripting solution. My supervisor came up with this code. If it can be done in plain SQL, how would I go about doing that?
Re: Code Help: Inserting Records based on records in another table meeting certain parameters [message #664873 is a reply to message #664871] Wed, 09 August 2017 12:10 Go to previous messageGo to next message
BlackSwan
Messages: 25639
Registered: January 2009
Location: SoCal
Senior Member
INSERT INTO afm_philadelphia.sd_wrcf_day
SELECT b.cf_id SYSDATE-1
FROM afm_philadelphia.sd_cf b
WHERE b.date_end > trunc(SYSDATE-30)
OR b.date_end IS NULL;
Re: Code Help: Inserting Records based on records in another table meeting certain parameters [message #664874 is a reply to message #664873] Wed, 09 August 2017 12:41 Go to previous messageGo to next message
cem616
Messages: 3
Registered: August 2017
Junior Member
That didn't do what I needed it to, but I figured it out on my own. Thanks anyway.
Re: Code Help: Inserting Records based on records in another table meeting certain parameters [message #664878 is a reply to message #664874] Wed, 09 August 2017 15:27 Go to previous messageGo to next message
BlackSwan
Messages: 25639
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/

"If you found an answer yourself, post it. That way we know the issue is resolved and others might learn from it."
Re: Code Help: Inserting Records based on records in another table meeting certain parameters [message #664952 is a reply to message #664873] Mon, 14 August 2017 14:07 Go to previous message
buggleboy007
Messages: 177
Registered: November 2010
Location: Canada
Senior Member
BlackSwan wrote on Wed, 09 August 2017 12:10
INSERT INTO afm_philadelphia.sd_wrcf_day
SELECT b.cf_id SYSDATE-1
FROM afm_philadelphia.sd_cf b
WHERE b.date_end > trunc(SYSDATE-30)
OR b.date_end IS NULL;
BS: Suggest you please read "How to use {code} tags and make your code easier to read" before posting your answers or code. Using tags you can make it easier for everyone to read.
Previous Topic: Error ORA-01722: Invalid use of Number in Select with Xmlagg / Xmlelement
Next Topic: PLS-00201: identifier 'UTL_MAIL' must be declared
Goto Forum:
  


Current Time: Mon Sep 25 21:09:43 CDT 2017

Total time taken to generate the page: 0.01120 seconds