4 Replies Latest reply: Dec 20, 2012 6:44 AM by G.Y RSS

    Arithmetic Calculation on NULL values in ORACLE FORMS

    G.Y
      Dear all ,
      Plz tell how to Add, Subtract values from NULL fields , I have the following Script,
      It is working fine in SQL.
      But In ORACLE FORMS it is not working fine, the SIZE_CODE field is null in some rows, there an error occuring ORA-01402,
      and likewise it doesn't calculate balance of null field(size_code)
      please help me how to handle it at form level.
      when i post the query at form.
      SELECT NVL(NVL(B.REQ_QNTY,0) - NVL(A.ISSUE_QNTY,0),0) INTO :ACC_ISSUE_C.BALANCE
      FROM
      (
      SELECT DISTINCT C.PO_CODE, L.COLOUR_NAME, I.ITEM_NAME, C.SIZE_CODE,
      PO_QNTY, C.REQ_QNTY, I.ITEM_ACC, C.ITEM_CODE, C.COLOUR_CODE
      FROM SCP.GENERAL_ITEMS I, PEM.ACC_REQUIRED C, PEM.BASIC_COLOURS L
      WHERE I.PO = 'Y'
      AND (I.ITEM_CODE = C.ITEM_CODE) AND C.COLOUR_CODE = L.COLOUR_CODE(+)
      )B,
      (SELECT ITEM_CODE,PO_CODE,COLOUR_CODE,SIZE_CODE,NVL(SUM(NVL(OUT_QNTY,0)),0) AS ISSUE_QNTY
      FROM SCP.ACC_ISSUE_C
      GROUP BY ITEM_CODE,PO_CODE,COLOUR_CODE,SIZE_CODE
      )A
      WHERE (B.PO_CODE = A.PO_CODE(+)) AND (B.COLOUR_CODE = A.COLOUR_CODE(+)) AND (B.ITEM_CODE = A.ITEM_CODE(+))
      AND (B.SIZE_CODE = A.SIZE_CODE(+))
      AND (B.PO_CODE = :ACC_ISSUE_C.PO_CODE) AND (B.COLOUR_CODE = NVL(:ACC_ISSUE_C.COLOUR_CODE,' '))
      AND (B.ITEM_CODE = :ACC_ISSUE_C.ITEM_CODE) AND (B.SIZE_CODE = nvl(:ACC_ISSUE_C.SIZE_CODE,' '))
      ORDER BY B.PO_CODE, B.COLOUR_CODE, B.ITEM_CODE;
      ------------------I am facing problem at these fields
      AND (B.SIZE_CODE = nvl(:ACC_ISSUE_C.SIZE_CODE,' '))
      AND (B.COLOUR_CODE = NVL(:ACC_ISSUE_C.COLOUR_CODE,' '))
      I try with both method like nvl,0 and nvl' '

      plz help