E
My solution is divided into several logical steps:The first CTE Call MiTabla simply simulate the underlying table with the data you have given example.Since there doesn't seem to be a sequential type field that can to easily sort the data, the first thing I do is to create to the flight (in the first CTE Call MiTablaOrdenada) a sequential field (from 1 to N) with the order that the rows should have. My suggestion is, if your underlying table has any field that will serve this effect, use it directly.Then, in the CTE BaseDescripcion I find all the rows that will serve to place the description to use in the column FixI also take the opportunity to find the previous registration number and the next registration row number. To make the algorithm work in a way independent with each new id in the underlying table I do partitions in the window function by the field id. This will make the engine leave NULL the field FilaAnterior for each first appearance of value TP0X in each new id, what it is vital to build the solution.Finally, I arm the solution in the CTE AsignaFix joining the following consultations:I get the ranks with cod TP0X directly from CTE BaseDescripcion, assigning the description itself to the field Fix. Besides, I bring the column. NumeroFila which will serve to order the final result.I get the ranks that are later of each row cod TP0X, making a https://es.stackoverflow.com/a/40/21 of BaseDescripcion with MiTablaOrdenada obtaining all the rows that belong to it id and are between NumeroFila of the base row of description and NumeroFilaSiguiente (without including them), and assigning descripcion obtained from BaseDescripcion to the field Fix.I finally get the rows that, for each id, are before the first appearance of cod TP0X, also with a ♪ between the same tables, only adjusting the condition. In a similarly unworthy way descripcion obtained from BaseDescripcion to the field Fix.In code, it looks like this:with
MiTabla as (
select '001' id, to_date('2020/01/01', 'yyyy/mm/dd') fecha, 'TP01' cod, 'fruta' descripcion from dual
union all select '001', to_date('2020/01/02', 'yyyy/mm/dd'), 'TP0X', 'comida' from dual
union all select '001', to_date('2020/01/03', 'yyyy-mm-dd'), 'TP03', 'verdura' from dual
union all select '001', to_date('2020/01/04', 'yyyy-mm-dd'), 'TP06', 'bebestible' from dual
union all select '001', to_date('2020/01/05', 'yyyy-mm-dd'), 'TP0X', 'licor' from dual
union all select '001', to_date('2020/01/05', 'yyyy-mm-dd'), 'TP10', 'legumbre' from dual
union all select '002', to_date('2020/01/03', 'yyyy-mm-dd'), 'TP03', 'verdura' from dual
union all select '002', to_date('2020/01/08', 'yyyy-mm-dd'), 'TP0X', 'comida' from dual
union all select '002', to_date('2020/01/09', 'yyyy-mm-dd'), 'TP0X', 'licor' from dual
union all select '002', to_date('2020/01/02', 'yyyy-mm-dd'), 'TP10', 'legumbre' from dual
)
,
MiTablaOrdenada as (
select row_number() over (order by id, fecha, cod) as NumeroFila
, id
, fecha
, cod
, descripcion
from MiTabla
)
,
BaseDescripcion as (
select lag(NumeroFila) over (partition by id order by NumeroFila) FilaAnterior
, NumeroFila
, lead(NumeroFila) over (partition by id order by NumeroFila) FilaSiguiente
, id
, fecha
, cod
, Descripcion
from MiTablaOrdenada
where Cod = 'TP0X'
)
,
AsignaFix as (
select NumeroFila, id, fecha, cod, Descripcion, Descripcion Fix
from BaseDescripcion
union all
select b.NumeroFila, b.id, b.fecha, b.cod, b.Descripcion, a.descripcion
from BaseDescripcion a
inner join MiTablaOrdenada b on b.id = a.id and b.NumeroFila > a.NumeroFila and b.NumeroFila < coalesce(a.FilaSiguiente, 999999999)
union all
select b.NumeroFila, b.id, b.fecha, b.cod, b.Descripcion, a.descripcion
from BaseDescripcion a
inner join MiTablaOrdenada b on b.id = a.id and b.NumeroFila < a.NumeroFila
where a.FilaAnterior is null
)
select *
from AsignaFix
order by NumeroFila
The result obtained is:NUMEROFILA ID FECHA COD DESCRIPCION FIX
1 001 01-JAN-20 TP01 fruta comida
2 001 02-JAN-20 TP0X comida comida
3 001 03-JAN-20 TP03 verdura comida
4 001 04-JAN-20 TP06 bebestible comida
5 001 05-JAN-20 TP0X licor licor
6 001 05-JAN-20 TP10 legumbre licor
7 002 02-JAN-20 TP10 legumbre comida
8 002 03-JAN-20 TP03 verdura comida
9 002 08-JAN-20 TP0X comida comida
10 002 09-JAN-20 TP0X licor licor
You can. https://livesql.oracle.com/apex/livesql/s/kcmwvb4d6h0ui2cct8y1uy51r on the site Oracle Live.Notice of editionThe answer has been adapted because the question was edited by the AP. The edition includes the case of having various id's in the input data and that the logic explained by the AP applies independently for each id.The original solution https://livesql.oracle.com/apex/livesql/s/kbid20tyqfmfnx0mwj9gslkk6 in oracle live.
