How to calculate Stock information?

[Rohullah]

Hello Sir

I have Error with the following tables one is [INCOME] And one is[Stock].

Income table Columns.
Date ItemID ItemType Amount DriverName TruckNo
---------------------
Stock Table columns.
Itemid ItemName ItemType Amount
-----------------------------------
Now when i enter record to income table
and income itemid not = to stock itemid then
itemid,itemName,ItemType,Amount Should be insert to the Stock table.
elsif income itemid = to stock itemID then
the stock the stock table should be update.
it means that income table amount Should Plus To Stock table amount
stock.amoutn + income.amount.
i try the following code but it does not work.
---------------------------------

declare
begin
if :income.itemid<>:stock.itemid then
insert into stock(itemid,itemname,itemtype,amount)
values(:income.itemid,:income.itemname,:income.itemtype,:income.amount);
        elsif :income.itemid=:stock.itemid then
        update stock set amount=amount+:income.amount where itemid=:stock.itemid;
    commit_form;
        go_block('Income');
    execute_query;
go_block('stock');
Execute_query;
    end if;
end; 

-----------------------------------
Please sir give me the Exact Answer
Thank you in advance.

[debasisdas]

How the above two mentioned tables are linked ?

[Rohullah]

They are not linked,Send me Passible answer Plz.

[amitpatel66]

This will not work because you are checking the current income.item_id with current stock.item_id.

You will need to query the stocks table to check if the entry for a particular item_id occurs in stocks table, if not then insert else update.

[Rohullah amiree]

ok send me the complete sulotion of this problem, I means what is the possible way to solve the problem thanks

[amitpatel66]

Here is the pseudo code:

 
SELECT count(*) into v_cnt from stock where item_id = :income.item_id;
 
IF v_cnt > 0 then
 
update stock
 
else
 
insert into stock values.....
 
 

I hope this helps.