CODEWITHSUNDEEP
sqloracle-databaseoracleforms
ali_codex
ali_codex

Reputation: 113

How to change value of variable in oracle form

i am tired to find out the solution please help me.

i want to know how to change the variable value of first table reels_table

through other variable of second table stockconsumption oracle form.

suppose

i have a paper stock with weight 400 kg i saved this in my oracle form.

next day i sold my paper stock with weight 200 kg.

now the actual reason that i want to do from couple of days, is

the left weight should be in first table variable for next transuction.

My code is below with picture ref .

DECLARE
REEL_ID1  NUMBER;
REEL_ID2  NUMBER;
LEFT_R1   NUMBER;
LEFT_R2   NUMBER;

BEGIN
       SELECT RID_P,
              RID_N,
              R_W_P_B,
              R_W_N_B 
         INTO REEL_ID1,
              REEL_ID2,
              LEFT_R1,
              LEFT_R2
         FROM STOCKCONSUMPTION
        WHERE :R_ID = REEL_ID1 
          AND :R_ID = REEL_ID2;

       :WEIGHT:=LEFT_R1;
END;

PICTURE IS HERE

Upvotes: 0

Views: 579

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

Code you wrote doesn't make much sense to me.

  • You've declared a whole lot of variables, but I don't think that you need any of them
  • WHERE condition is suspicious; it seems that you used local variables along with block items. Furthermore, how probable is that :R_ID is equal to both REEL_ID1 and REEL_ID2?

I'd try with something like this:

select s.r_w_p_b
  into :reels_table.weight
  from stockconsumption s
  where s.rid_p = :reels_table.r_id
    and s.rid_n = :reels_table.r_id;

As you can see:

  • no variables declared, as I
    • selected directly into the block item
    • used items' values in WHERE clause (once again, I think it is wrong, but you should know better)

However, if I understood what you're doing - you're doing it wrong. Stock shouldn't be kept as a static value in any table - compute it any time you need it. It will become difficult to maintenance integrity in multi-user environment, when many people perform DML over the same data set. Here's an example which shows a general principle.

SQL> create table stock
  2    (item_id          number,
  3     transaction_type varchar2(1),  -- B - bought; S - sold
  4     weight           number
  5    );

Table created.

SQL> -- bought 400 kg; stock =  400
SQL> insert into stock values (1234, 'B', 400);

1 row created.

SQL> select item_id,
  2         sum(decode(transaction_type, 'B', weight, 'S', -weight)) stock
  3  from stock
  4  group by item_id;

   ITEM_ID      STOCK
---------- ----------
      1234        400

SQL> -- bought additional 600 kg; stock = 1000
SQL> insert into stock values (1234, 'B', 600);

1 row created.

SQL> select item_id,
  2         sum(decode(transaction_type, 'B', weight, 'S', -weight)) stock
  3  from stock
  4  group by item_id;

   ITEM_ID      STOCK
---------- ----------
      1234       1000

SQL> -- sold   300 kg; stock =  700
SQL> insert into stock values (1234, 'S', 300);

1 row created.

SQL> select item_id,
  2         sum(decode(transaction_type, 'B', weight, 'S', -weight)) stock
  3  from stock
  4  group by item_id;

   ITEM_ID      STOCK
---------- ----------
      1234        700

SQL>

See? No additional table(s) nor column(s) to keep current state - a simple SELECT returns the current value. Think about it, see if it makes sense in your case.

Upvotes: 2

Related Questions