1

Although it's much more complex than I'm about to explain, I'll try to only stick to the relevant bits of what I want to accomplish. Our data model is quite complex, and the terms are also a bit confusing. We basically have a Request, and this request can have an active Request_Status (which has an Enum_Value to indicate it's current status), as well as previous Request_Statuses that aren't relevant anymore (to preserve history). A Person is linked to this Request, but the Values that are entered are linked to the current Request_Status.

So here are those tables, and the relevant columns:

Persons:

  • person_id
  • unique_code
  • Some other values

Requests:

  • request_id
  • fk_person_id
  • year
  • Some other values

Enum_Values:

  • enum_value_id
  • value
  • Some other values

Request_Statuses:

  • request_status_id
  • fk_request_id
  • fk_enum_value_id
  • created_date
  • Some other values

Values:

  • value_id
  • fk_request_status_id
  • Some other values

I have: A list of Person.unique_codes.
I want to achieve two things:

  1. For each Person.unique_code I want to get the Request of the year 2017, and then create a new Request_Status with fk_enum_value_id set to 4, linked to this existing Request.
  2. Create copies of the Values that were linked to the previously active Request_Status, and set their fk_request_status_id to the currently active Request_Status (the records I've created in step 1).

I've been able to do step 1 myself with a monstrous query (but it works..)
Here is the monstrous query for step 1:

Some things to note:
- There will only be a single Request of a given year.
- There can be more than one Request_Statuses for a given Request, so finding the active is the one with the highest created_date.
- p.unique_code IN ('12345','67890') is privatized and reduced code. In reality I have about 500 person.unique_codes. - SELECT rs1.fk_request_id, 4 /*, some other irrelevant values */ FROM Request_Statuses rs1 LEFT JOIN Request_Statuses rs2 ON (rs1.fk_request_id = rs2.fk_request_id AND rs1.created_date < rs2.created_date) WHERE rs2.created_date IS NULL is copied from this SO answer for the question "Retrieving the last record in each group". I've used the windowing function at the top before, but it wasn't really suitable for sub-queries in combination with Oracle SQL, so I've used the (probably slightly slower) original method that was posted in 2009, which does work as intended.

INSERT_INTO Request_Statuses (fk_request_id, fk_enum_value_id /*, some other irrelevant values */)
(SELECT rs1.fk_request_id, 4 /*, some other irrelevant values */ FROM Request_Statuses rs1
LEFT JOIN Request_Statuses rs2 ON (rs1.fk_request_id = rs2.fk_request_id AND rs1.created_date < rs2.created_date)
WHERE rs2.created_date IS NULL AND rs1.fk_request_id IN (SELECT r.request_id FROM Requests r
WHERE r.fk_person_id IN (SELECT p.person_id FROM Persons p
WHERE p.unique_code IN ('12345','67890')) AND r.year = 2017));

And I'm currently working on step 2.
I currently have this:

INSERT INTO Values (fk_request_status_id /* some other irrelevant values */)
(SELECT /*TODO: Get request_status_id created in step 1*/, /* some other irrelevant values */
FROM Values v1 WHERE v1.fk_request_status_id IN (SELECT rs.status_id FROM Request_Statuses rs
WHERE rs.fk_request_id IN (SELECT r.request_id FROM Requests r
WHERE r.fk_person_id IN (SELECT p.person_id FROM Persons p
WHERE p.bsn IN ('12345','67890')) AND r.year = 2017) AND (SELECT COUNT(*) FROM Values v2
WHERE v2.fk_request_status_id = rs.status_id) > 0));

All I need is to get the request_status_id of the Request_Statuses I've created in step 1, based on the same person.unique_code, and insert it at the TODO..

I've also been thinking about using a default value for now, and then update just the fk_request_status_id with a third (monstrous) query. Unfortunately, the fk_request_status_id in combination with a second column in the Values table form an unique constraint, and fk_request_status_id cannot be empty, so I can't just insert any value here to update later.. Maybe I should remove the constraints temporarily, and add them later again after the query..

PS: Performance isn't that important. I've only got around 500-750 person.unique_codes for which I have to create one new Request_Status each (and zero to about 50 Values that are potentially linked to the previous active Request_Status). It should work in under 4 hours, though. ;)

Kevin Cruijssen
  • 9,153
  • 9
  • 61
  • 135
  • 1
    Maybe the simplest option is to CTAS VALUES_TEMP table, fill it with desired data (no matter how *monstruous* code you use), and then copy it back to VALUES. – Littlefoot Jan 05 '18 at 15:38
  • @Littlefoot Hmm, that might not be a bad idea. Just a `TEMP_Values` table that is an exact copy of `Values`, but without the constraints, and then just use two separated queries. I'll probably do that for now, unless someone comes up with a better approach. Thanks for the suggestion. – Kevin Cruijssen Jan 05 '18 at 15:42
  • Exactly; without constraints, you can write any code you want - UPDATE back and forth until you're satisfied with the result. Though, the final result should really be OK, otherwise INSERT INTO VALUES will fail (which is OK; it would be bad if invalid values came into VALUES table). – Littlefoot Jan 05 '18 at 15:48
  • It would be much easier for us to grasp your requirements if you could update your question to include sample data for each of your tables (we only need data for the required columns, and it can be test data), along with the output you're looking to obtain. That way, we can play around with the data ourselves. – Boneist Jan 05 '18 at 16:26
  • 1
    @Boneist I'm currently at home, but I will do that on Monday. Thanks for the suggestion. – Kevin Cruijssen Jan 05 '18 at 18:35

0 Answers0