Update in a field that cannot be negative

Question

I need to make a update in a given field, where it should reach at least up to "zero" cannot be negative. And when it reaches zero I need it to give the update of the remaining quantity in the next field. "".

Example: I’m going down 9948

Unit Quantity      Expira
    9947         10/10/2017
    9947         10/11/2017

Wrong shape :

Unit Quantity      Expira
    -1           10/10/2017
    9947         10/11/2017

Correct form :

Unit Quantity      Expira
     0           10/10/2017
    9946         10/11/2017

Query :

UPDATE invdtl
   SET untqty = untqty - 9948
 WHERE prtnum = 'OP-CX44LT'
   AND invsts = 'LIB'
   AND lst_arecod IN ('1SUPRCK')
   AND expire_dte <= TRUNC(expire_dte)

Answer 1

You could use an analytical function to identify the records that will need to be updated, the function greatest to prevent a certain value from being negative, and the command merge ( or a simple loop ) to refresh the column.

Something like:

merge into invdtl a
using (
  select a.*
       , greatest( untqty - :quantidade_a_baixar, 0 ) nova_quantidade
  from (
    select rowid rid
         , untqty
         , sum( untqty ) over( order by to_number( expire_dte ) ) - untqty qtd_acumulada
    from   invdtl
    where  prtnum      = 'OP-CX44LT'
    and    invsts      = 'LIB'
    and    lst_arecod in ('1SUPRCK')
    and    expire_dte <= trunc(expire_dte)
  ) a
  where  acumulado < :quantidade_a_baixar
) b on ( a.rowid = b.rid )
when matched then
  update set a.untqty = b.nova_quantidade

Answer 2

That’s how it’s really in the field but on a different record

Answer 3

In thesis (just testing) a update Trigger in the table could solve :

Something like

CREATE TRIGGER BEFORE UPDATE IN TABELA ...

IF :NEW.VALOR < 0 THEN
   :NEW.VALOR := 0;
   INSERT INTO TABELA (...,VALOR,...) VALUES (.....ABS(:NEW-VALOR),....);
END IF;

if the value is negative if "Zera" the same and if throws it positive in another record.