I have a constr_events
table:
create table constr_events
(
event_id number(4) not null unique,
road_id number(4) not null,
constr_date date not null,
reset_condition number(9,2) not null
) ;
insert into constr_events (event_id, road_id, constr_date, reset_condition) values (1,400,to_date('2016-11-01','YYYY-MM-DD'),1);
insert into constr_events (event_id, road_id, constr_date, reset_condition) values (2,500,to_date('2009-11-01','YYYY-MM-DD'),1);
insert into constr_events (event_id, road_id, constr_date, reset_condition) values (3,500,to_date('2031-11-01','YYYY-MM-DD'),1);
select
event_id,
road_id,
to_char(constr_date,'YYYY-MM-DD') as constr_date_formatted,
reset_condition
from
constr_events
order by
road_id,
constr_date;
EVENT_ID ROAD_ID CONSTR_DATE_FORMATTED RESET_CONDITION
---------- ---------- --------------------- ---------------
1 400 2016-11-01 1
2 500 2009-11-01 1
3 500 2031-11-01 1
And a road_insp
table:
create table road_insp
(
insp_id number(4) not null unique,
road_id number(4) not null,
insp_date date not null,
condition number(9,2) not null
) ;
insert into road_insp (insp_id, road_id, insp_date, condition) values (40,400,to_date('1960-05-01','YYYY-MM-DD'),.8);
insert into road_insp (insp_id, road_id, insp_date, condition) values (41,400,to_date('2009-05-01','YYYY-MM-DD'),.3);
insert into road_insp (insp_id, road_id, insp_date, condition) values (42,400,to_date('2012-05-01','YYYY-MM-DD'),.3);
insert into road_insp (insp_id, road_id, insp_date, condition) values (43,400,to_date('2015-05-01','YYYY-MM-DD'),.2);
insert into road_insp (insp_id, road_id, insp_date, condition) values (45,400,to_date('2019-05-01','YYYY-MM-DD'),.9);
--insert into road_insp (insp_id, road_id, insp_date, condition) values (50,500,to_date('1994-05-01','YYYY-MM-DD'),.6);
--insert into road_insp (insp_id, road_id, insp_date, condition) values (90,500,to_date('2006-05-01','YYYY-MM-DD'),.30);
insert into road_insp (insp_id, road_id, insp_date, condition) values (67,500,to_date('2010-05-01','YYYY-MM-DD'),.9);
insert into road_insp (insp_id, road_id, insp_date, condition) values (68,500,to_date('2014-05-01','YYYY-MM-DD'),.9);
insert into road_insp (insp_id, road_id, insp_date, condition) values (69,500,to_date('2017-05-01','YYYY-MM-DD'),.9);
insert into road_insp (insp_id, road_id, insp_date, condition) values (70,500,to_date('2020-05-01','YYYY-MM-DD'),.8);
insert into road_insp (insp_id, road_id, insp_date, condition) values (71,500,to_date('2023-05-01','YYYY-MM-DD'),.8);
insert into road_insp (insp_id, road_id, insp_date, condition) values (72,500,to_date('2026-05-01','YYYY-MM-DD'),.7);
insert into road_insp (insp_id, road_id, insp_date, condition) values (73,500,to_date('2029-05-01','YYYY-MM-DD'),.6);
insert into road_insp (insp_id, road_id, insp_date, condition) values (74,500,to_date('2032-05-01','YYYY-MM-DD'),.9);
insert into road_insp (insp_id, road_id, insp_date, condition) values (75,500,to_date('2035-05-01','YYYY-MM-DD'),.9);
commit;
select
insp_id,
road_id,
to_char(insp_date,'YYYY-MM-DD') as insp_date_formatted,
condition as condition
from
road_insp
order by
road_id,
insp_date;
INSP_ID ROAD_ID INSP_DATE_FORMATTED CONDITION
---------- ---------- ------------------- ----------
40 400 1960-05-01 .8
41 400 2009-05-01 .3
42 400 2012-05-01 .3
43 400 2015-05-01 .2 --last inspection before construction
45 400 2019-05-01 .9
67 500 2010-05-01 .9
68 500 2014-05-01 .9
69 500 2017-05-01 .9
70 500 2020-05-01 .8
71 500 2023-05-01 .8
72 500 2026-05-01 .7
73 500 2029-05-01 .6 --last inspection before construction
74 500 2032-05-01 .9
75 500 2035-05-01 .9
When I union the tables together, they look like this:
select
road_id,
to_char(insp_date,'YYYY-MM-DD') as condition_date,
condition as condition,
'INSPECTION' as type
from
road_insp
union all
select
road_id,
to_char(constr_date,'YYYY-MM-DD') as condition_date,
reset_condition,
'CONSTRUCTION' as type
from
constr_events
order by
road_id,
condition_date
ROAD_ID CONDITION_DATE CONDITION TYPE
---------- -------------- ---------- ------------
400 1960-05-01 .8 INSPECTION
400 2009-05-01 .3 INSPECTION
400 2012-05-01 .3 INSPECTION
400 2015-05-01 .2 INSPECTION
400 2016-11-01 1 CONSTRUCTION
400 2019-05-01 .9 INSPECTION
500 2009-11-01 1 CONSTRUCTION
500 2010-05-01 .9 INSPECTION
500 2014-05-01 .9 INSPECTION
500 2017-05-01 .9 INSPECTION
500 2020-05-01 .8 INSPECTION
500 2023-05-01 .8 INSPECTION
500 2026-05-01 .7 INSPECTION
500 2029-05-01 .6 INSPECTION
500 2031-11-01 1 CONSTRUCTION
500 2032-05-01 .9 INSPECTION
500 2035-05-01 .9 INSPECTION
For each construction event, I want to generate a dummy road inspection that gets the condition from the previous inspection.
The dummy inspection would have these attributes:
road_id
from the the construction eventcondition_date
from the construction event, but it would be January 1st of that yearcondition
from the previous road inspectiontype
as "CARRYOVER FROM PREVIOUS INSPECTION"
It would look like this:
ROAD_ID CONDITION_DATE CONDITION TYPE
---------- -------------- ---------- ------------
400 1960-05-01 .8 INSPECTION
400 2009-05-01 .3 INSPECTION
400 2012-05-01 .3 INSPECTION
400 2015-05-01 .2 INSPECTION
400 2016-01-01 .2 CARRYOVER FROM PREVIOUS INSPECTION
400 2016-11-01 1 CONSTRUCTION
400 2019-05-01 .9 INSPECTION
500 2009-11-01 1 CONSTRUCTION
500 2010-05-01 .9 INSPECTION
500 2014-05-01 .9 INSPECTION
500 2017-05-01 .9 INSPECTION
500 2020-05-01 .8 INSPECTION
500 2023-05-01 .8 INSPECTION
500 2026-05-01 .7 INSPECTION
500 2029-05-01 .6 INSPECTION
500 2031-01-01 .6 CARRYOVER FROM PREVIOUS INSPECTION
500 2031-11-01 1 CONSTRUCTION
500 2032-05-01 .9 INSPECTION
500 2035-05-01 .9 INSPECTION
How can I create a query that generates the dummy construction events?
Update:
I've come across situations where a road's first record is a constr_event
, not an insp_event
. I've updated the DDL to reflect this scenario (deleted inspections #50
and #90
).
When this is the case, @Joe Obbish's query returns null values for the condition
. Preferably, instead of returning a record with a null, the queries would simply not return a record at all.
Is there an elegant way to force the query to do this, or is the only solution to wrap the query in a subselect and use a where clause (where condition is not null
)?
Best Answer
To fill in the missing rows you need exactly one row per row in the
constr_events
table. It's straightforward to get these three columns just by querying that table:That leaves just this one:
A correlated subquery can be a good option when you need to pull exactly one column value from another table. You're on Oracle 12c, so you can take advantage of the expanded
ORDER BY
syntax. Here's one way to get the results that you want:Depending on the size of your tables you may want an index on the
road_id
andinsp_date
columns of theroad_insp
table. Consider what would happen if the query optimizer implemented the subquery with a nested loop join and there was no index. You would do a full scan of theroad_insp
table for each row inconstr_events
. An index as described earlier allows Oracle to jump to the relevantroad_id
and to find the relevantinsp_date
without scanning all of theinsp_date
values. I would expect it to just be a handful of logical reads for each row inconstr_events
.If you need to exclude rows with a NULL carryover you can do this by changing the subquery to use
CROSS APPLY
,LATERAL JOIN
, or by wrapping the whole thing in a derived table and filtering on that column outside of the derived table.