Oracle - Inserting new rows between query result

Question

I've got a tricky task here. Here's the sample data:

create table incidents (
incident_id number,
region_id varchar2(100 char),
tasktype varchar2(100 char),
department_from varchar2(100 char),
department_to varchar2(100 char),
routing_date date,
failure_from date,
failure_to date,
incident_acception_date date,
incident_resolve_date date
);

Insert into incidents (incident_id,region_id,Tasktype,department_from,department_to,routing_date,failure_from,failure_to,incident_acception_date,incident_resolve_date) values (1,'RE2','Task','Group1','Group2',to_date('10.04.2015 23:54:21','dd.mm.yyyy hh24:mi:ss'),to_date('26.03.2015 22:00:00','dd.mm.yyyy hh24:mi:ss'),to_date('13.04.2015 18:25:00','dd.mm.yyyy hh24:mi:ss'),to_date('26.03.2015 22:32:00','dd.mm.yyyy hh24:mi:ss'),to_date('13.04.2015 18:50:07','dd.mm.yyyy hh24:mi:ss'));
Insert into incidents (incident_id,region_id,Tasktype,department_from,department_to,routing_date,failure_from,failure_to,incident_acception_date,incident_resolve_date) values (1,'RE2','Task','Group2','Group3',to_date('13.04.2015 07:19:05','dd.mm.yyyy hh24:mi:ss'),to_date('26.03.2015 22:00:00','dd.mm.yyyy hh24:mi:ss'),to_date('13.04.2015 18:25:00','dd.mm.yyyy hh24:mi:ss'),to_date('26.03.2015 22:32:00','dd.mm.yyyy hh24:mi:ss'),to_date('13.04.2015 18:50:07','dd.mm.yyyy hh24:mi:ss'));
Insert into incidents (incident_id,region_id,Tasktype,department_from,department_to,routing_date,failure_from,failure_to,incident_acception_date,incident_resolve_date) values (2,'RE2','Task','Group1','Group4',to_date('01.04.2015 21:26:16','dd.mm.yyyy hh24:mi:ss'),to_date('30.03.2015 13:00:00','dd.mm.yyyy hh24:mi:ss'),to_date('02.04.2015 09:30:00','dd.mm.yyyy hh24:mi:ss'),to_date('30.03.2015 14:09:00','dd.mm.yyyy hh24:mi:ss'),to_date('02.04.2015 10:06:13','dd.mm.yyyy hh24:mi:ss'));
Insert into incidents (incident_id,region_id,Tasktype,department_from,department_to,routing_date,failure_from,failure_to,incident_acception_date,incident_resolve_date) values (2,'RE2','Task','Group4','UNASSIGNED',to_date('01.04.2015 22:45:14','dd.mm.yyyy hh24:mi:ss'),to_date('30.03.2015 13:00:00','dd.mm.yyyy hh24:mi:ss'),to_date('02.04.2015 09:30:00','dd.mm.yyyy hh24:mi:ss'),to_date('30.03.2015 14:09:00','dd.mm.yyyy hh24:mi:ss'),to_date('02.04.2015 10:06:13','dd.mm.yyyy hh24:mi:ss'));
Insert into incidents (incident_id,region_id,Tasktype,department_from,department_to,routing_date,failure_from,failure_to,incident_acception_date,incident_resolve_date) values (2,'RE2','Task','UNASSIGNED','Group2',to_date('01.04.2015 22:45:32','dd.mm.yyyy hh24:mi:ss'),to_date('30.03.2015 13:00:00','dd.mm.yyyy hh24:mi:ss'),to_date('02.04.2015 09:30:00','dd.mm.yyyy hh24:mi:ss'),to_date('30.03.2015 14:09:00','dd.mm.yyyy hh24:mi:ss'),to_date('02.04.2015 10:06:13','dd.mm.yyyy hh24:mi:ss'));
Insert into incidents (incident_id,region_id,Tasktype,department_from,department_to,routing_date,failure_from,failure_to,incident_acception_date,incident_resolve_date) values (3,'RE2','Information','UNASSIGNED','Group1',to_date('01.04.2015 07:31:27','dd.mm.yyyy hh24:mi:ss'),to_date('01.04.2015 06:00:00','dd.mm.yyyy hh24:mi:ss'),to_date('11.06.2015 07:06:00','dd.mm.yyyy hh24:mi:ss'),to_date('01.04.2015 07:20:00','dd.mm.yyyy hh24:mi:ss'),to_date('16.06.2015 14:17:25','dd.mm.yyyy hh24:mi:ss'));
commit;

It looks as follows:

My aim is to create for each incident

one new preceding record and
one new succeeding record.

It needs to be done without a temporary table (WITH AS would be ok).

The desired output looks like this:

The arrows show where the data for the new column is taken from. Rules:

If the first record contains DEPARTMENT_FROM='UNASSIGNED', the new preceeding record will be DEPARTMENT_FROM='1st-Level-Agent', else DEPARTMENT_FROM='UNASSIGNED'.

But I think the pictures makes it pretty clear.

How could this be achieved?

Thank you very much in advanced!

"preceding" and "succeeding" make no sense in relational databases. You need to think your requirements through more carefully. — duffymo
– duffymo, Commented Oct 7, 2015 at 11:20
I want to create a new row which precedes the first existing row and a new row which succeeds the last existing row - per incident in this case. Why does it make no sense? I want to "enrich" the existing data in this way for further analysis. — royskatt
– royskatt, Commented Oct 7, 2015 at 11:26
I understand what you mean by preceding and succeeding. But you cannot guarantee that the retrieval of data will be in same order as it was inserted, unless you have a unique identifier and you order by it. So if you insert 5 names in db, and select them again, you might not get them in the same order as they were inserted. Hence there is no preceding and succeeding in relational db. To achieve what you want, you have to first add a key and order by it. Then you can have any preceding and succeeding rows — Utsav
– Utsav, Commented Oct 7, 2015 at 12:01
Perhaps writing a pre-INSERT trigger is what you need: When a record is INSERTed, have the trigger create new rows. — duffymo
– duffymo, Commented Oct 7, 2015 at 12:09
Sure, actually I would need to order by INCIDENT_ID, REGION_ID, ROUTING_DATE. That would be th PK. Afterwards I thought of an array of arrays of arrays. All incidents will be saved as item in Array1. Each item(Array1) itself is Array2 containing each row per incident in the desired order. Each item(Array2) contains the columns(Array3). I can now compare the colums values of the rows with each other and can create new rows before and after. That's the idea. I knew how to do that in Python but in PL/SQL that seems somehow very complicated to me. — royskatt
– royskatt, Commented Oct 7, 2015 at 12:34

Bob Jarvis - Слава Україні · Accepted Answer · 2015-10-07 11:30:23Z

3

Rows in a table do not have any implicit ordering, and if you do not supply an ORDER BY clause in your SELECT statement the database is free to return the rows in whatever order it wants to because you haven't told it how to order the returned rows. Without an ORDER BY clause there is no "first row" and no "last row". If you want things ordered you need to have a field (I often have one named SORT_ORDER) which defines how the rows should be sorted. Thus, if I already have a row with SORT_ORDER = 1, then I can add a "preceding" row by inserting a new row with SORT_ORDER = 0. Similarly I can add a "following" row by inserting a new row with SORT_ORDER = 2. But when querying this table I need to remember to specify ORDER BY SORT_ORDER.

Best of luck.

answered Oct 7, 2015 at 11:30

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Benjamin · Accepted Answer · 2015-10-07 12:18:16Z

I try to give you an example which you can adapt to your needs. I used hierarchical queries to formulate "preciding" and "succeeding". Through union I joined them to the original set of data. I used the WITH to build up the example data. Hope that helps!

with 
   incidents as (
      select 1 as id, 'group 1' as "from", 'group 2' as "to" from dual
      union 
      select 1 as id, 'group 2' as "from", 'group 3' as "to" from dual
      union 
      select 2 as id, 'UNASSIGNED' as "from", 'G1' as "to" from dual
      )
select 0      as rwn, 
       id, 
       ( 
         case
           when "from" = 'UNASSIGNED' 
             then '1st-Level-Agent' 
             else 'UNASSIGNED'
         end
       )      as "from", 
       "from" as "to"
  from ( select t.*, level as lvl, connect_by_isleaf as leaf
           from incidents t 
        connect by prior "from" = "to" )
 where lvl = 1 
   and leaf = 1
union
select rownum as rwn, t2.* from incidents t2
union 
select 1000       as rwn, 
       id, 
       "to"       as "from", 
       'RESOLVED' as "to"
  from ( select t.*, level as lvl, connect_by_isleaf as leaf  
           from incidents t 
        connect by "from" = prior "to" )
 where lvl = 1 
   and leaf = 1
order by 2,1

Collectives™ on Stack Overflow

Oracle - Inserting new rows between query result

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related