Why does it have to be one query?
Why not retrieve the column headers in one step, pull the real data in another and then stitch the two together, if that's even required?
If this is being done under application control (as opposed to a single database "query"), you may not need to do this "reassembly" at all. It might be possible to ...
Using the expression
result = -(x & mask) + (x & !mask)
where mask=2^(n-1) yields the result you want. The (x & mask) isolates the sign bit and (x & !mask) is the offset. When the sign bit is not set, (x & mask) == 0 and (x & !mask) is the result. When the sign bit is set, -(x & mask) == -2^(n-1) and (x & !mask) is the ...
Why are you purging 2 billion rows in one go?
If that's the entire content of the table, why not just truncate it?
If you really must do this in one go, you'll have to "upgrade" from PLS_INTEGER to INTEGER (a predefined subtype of NUMBER). OK, you lose the efficiencies of PLS_INTEGER, but you also lose the 32-bit limitation that it imposes on you.
I found a better solution. Instead of Last_value() function , I used First_value() and order by t.operation_date desc:
first_value(t.amount) over(partition by t.col_id order by t.operation_date desc ) amount
from doc_amount_detail t
where t.operation_date <= to_date('11/15/2020', 'mm/dd/yyyy');
The ORDER BY clause (in an analytical function), implies a window clause of ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
You need ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
Since this will apply to each row, you'll need a DISTINCT clause.
with data(col_id, amount, operation_date ) as (
select 1, 5000, to_date( '...
rank() over (partition by d.col_id order by d.operation_date desc) as rk
from doc_amount_detail d
where.d.operation_date <= to_date('11/15/2020', 'mm/dd/yyyy')
where rk = 1;
I think you are fighting against how Oracle works with implicit type conversion but you could suppress that conversion with custom types. Looks ugly but kind of does what you asked:
drop type vtype;
drop type ntype;
create type vtype as object(vvar varchar2(32767));
create type ntype as object(nvar NUMBER);