Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2013
    Posts
    2

    Unanswered: when NO_DATA_FOUND return zero

    I'm using oracle 11g

    I have two tables Orderitems:

    "ORDER_ITEMS_CODE" VARCHAR2(20) NOT NULL ENABLE,
    "ORDER_CODE" VARCHAR2(20) NOT NULL ENABLE,
    "ITEM_CODE_ORDERS" VARCHAR2(20) NOT NULL ENABLE,
    "ORDER_QUANTITY" NUMBER(4,0) NOT NULL ENABLE,
    "ORDER_UNIT" VARCHAR2(5) NOT NULL ENABLE,
    "UNIT_PRICE" NUMBER(38,5),
    "ORDERED_IN" VARCHAR2(6),
    "OR_QUANTITY_TON" NUMBER(38,5),
    "Warehouse_CODE" VARCHAR2(20) NOT NULL ENABLE
    the other table is Inventory:

    "INVENTORY_CODE" VARCHAR2(20) NOT NULL ENABLE,
    "ITEM_CODE" VARCHAR2(20) NOT NULL ENABLE,
    "WAREHOUSE_CODE" VARCHAR2(20),
    "IN_Q_TON" NUMBER(38,5),
    "OR_Q_TON" NUMBER(38,5)
    I created a trigger to calculate "OR_QUANTITY_TON" = Sum ("OR_QN_TON")

    create or replace trigger sum_Or_IT
    after insert or update or delete on orderitems
    begin
    update INVENTORY set OR_Q_TON = (
    select sum(or_quantity_ton) from orderitems
    where
    orderitems.item_code_orders = INVENTORY.item_code
    and
    warehouse_code = '1');
    end;
    this is an inventory table and the column OR_Q_TON is the sum of the ordered quantity for every item,, but if an item doesn't exist in the orders ,, I want the order quantity to be zero. I want to make an exception if the "item_code_orders" doesn't exist in the table "Orderitems" then the OR_Q_TON = 0

    I tried this but it didn't work I got (-) not (0)

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    update INVENTORY set OR_Q_TON = 0 ;

  2. #2
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    Have you tried coalesce?
    select coalesce(sum(or_quantity_ton),0) from orderitems


    (It may be called something else in Oracle, coalesce is ANSI/ISO standard.)

  3. #3
    Join Date
    Aug 2013
    Posts
    2
    yes it worked ,,, thank you very much

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •