Update in a field that can not be negative

Question

Update in a field that can not be negative

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

0

I need to do a update in a certain field, where it should arrive at least until "zero" can not be negative. And when I get to zero I need it from the update of the remaining amount in the next field. "The setting is for it to drop the first amount in the record with the lower one expiring" FIFO. "

Example: I'm going to dial 9948

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

Wrong form:

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)

mysql sql oracle

asked by anonymous 05.01.2017 / 19:29

3 answers

Target Unreachable, identifier 'users' resolved to null Running a Background Method Asp.Net/C# (Async or Thread)

score 1 · Answer 1

You could use an analytical function to identify the records that need to be updated, the greatest function to not allow a given value to be negative, and the merge (or a simple loop) command to update the column. / p>

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

score 0 · Answer 2

0

This is the same field but in different registry

06.01.2017 / 10:24

score 0 · Answer 3

In thesis (just testing) an 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 becomes negative, it "zeroes" the value and sends it positive in another register.