I have the following data which I'm trying to clean.

My goal is, that whenever there are records with the same vehicle and replacement is not NULL and there is a record where replacement is NULL (for the same vehicle) and the TO attribute of the record with replacement NULL equals the FROM attribute of the record with replacement not NULL, it should replace the START attribute in all records where the replacement IS NOT NULL and same vehicle with the START attribute of the on match above.

So the result should look like the following:

Notice, that there are other entries with vehicle = 2 which stay unaffected (W-Z)

I already tried a lot in pure SQL with lead and lag, so I could replace the first row (FROM E to F) but I wasn't able to replace all necessary rows, because there was no criteria for me to use. So I guess there's no way around using PL/SQL and do it procedural, but I don't know exactly how to solve that. I can do some PL/SQL, but I'm no expert in it.

Here's the set of the table above to use. I would appreciate any help very much.

WITH a AS (
SELECT 2 "vehicle", 'A' "FROM", 'B' "TO", 'A' "START", NULL "replacement" FROM dual
UNION ALL 
SELECT 2 "vehicle", 'B' "FROM", 'C' "TO", 'A' "START", NULL "replacement" FROM dual
UNION ALL 
SELECT 2 "vehicle", 'C' "FROM", 'D' "TO", 'A' "START", NULL "replacement" FROM dual
UNION ALL 
SELECT 2 "vehicle", 'D' "FROM", 'E' "TO", 'A' "START", NULL "replacement" FROM dual
UNION ALL 
SELECT 2 "vehicle", 'E' "FROM", 'F' "TO", 'E' "START", 123 "replacement" FROM dual
UNION ALL 
SELECT 2 "vehicle", 'G' "FROM", 'H' "TO", 'E' "START", 123 "replacement" FROM dual
UNION ALL 
SELECT 2 "vehicle", 'I' "FROM", 'J' "TO", 'E' "START", 123 "replacement" FROM dual
UNION ALL 
SELECT 2 "vehicle", 'W' "FROM", 'X' "TO", 'W' "START", NULL "replacement" FROM dual
UNION ALL 
SELECT 2 "vehicle", 'X' "FROM", 'Y' "TO", 'W' "START", NULL "replacement" FROM dual
UNION ALL 
SELECT 2 "vehicle", 'Y' "FROM", 'Z' "TO", 'W' "START", NULL "replacement" FROM dual
UNION ALL 
SELECT 3 "vehicle", 'Q1' "FROM", 'Q2' "TO", 'W' "START", NULL "replacement" FROM dual
UNION ALL 
SELECT 3 "vehicle", 'Q2' "FROM", 'Q3' "TO", 'W' "START", NULL "replacement" FROM dual
)
SELECT * FROM a;

CodePudding user response：

You can use match_recognize for that:

Just to make it easier to understand, I've added your required result as NEW_START column.

DBFiddle: