New answers tagged

0

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 ...


1

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 ...


0

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. ...


0

I found a better solution. Instead of Last_value() function , I used First_value() and order by t.operation_date desc: select distinct t.col_id, 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');


3

Explaination 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. Example with data(col_id, amount, operation_date ) as ( select 1, 5000, to_date( '...


1

Analytic Functions select col_id, amount from ( select d.col_id, d.amount, 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;


0

I got the answer to this question. https://dbfiddle.uk/?rdbms=oracle_18&fiddle=412a6e27a22741ee1c31eee4a3f2bf3a


2

Why do you not simply unpivot the hole table SELECT * FROM push_data_temp; ID_PK | ID | COL1 | COL2 ...


2

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); / DECLARE PROCEDURE do_something( in_ ...


Top 50 recent answers are included