一个表中的值减去另一台价值

Question

我想（加入p.L_VALUE和p.I_VALUE而且这个结果，减c.con_value）减去一列在不同的表中的另一列PLSQL功能的另一表值，函数下面编译成功但减的结果是不正确的：

function CF_OP_QTYFormula return Number is 
    V_QTY number(10); 
begin 
    select sum(nvl(p.L_QTY, 0)) + sum(nvl(p.I_QTY, 0)) - sum(nvl(c.CON_QTY,0)) 
    into V_QTY 
    from raw_purchase p, raw_consum c 
    where p.ITEMCODE = c.ITEMCODE 
    and p.ITEMCODE = :ITEMCODE 
    and p.MONTH_YEAR between '31-MAY-2010' 
    and ADD_MONTHS(:date1, -1); 
    return v_qty; 
    exception when no_data_found then return null; 
end; 

---

Answer 1

试试这个请：

function CF_OP_QTYFormula(i_ITEMCODE raw_purchase.ITEMCODE%type, i_date1 date) return Number is 
    V_QTY number(10); 
begin 
    select sum(nvl(p.L_QTY, 0) + nvl(p.I_QTY, 0) - nvl(c.CON_QTY,0)) 
    into V_QTY 
    from raw_purchase p, raw_consum c 
    where p.ITEMCODE = c.ITEMCODE 
    and p.ITEMCODE = i_ITEMCODE 
    and p.MONTH_YEAR between to_date('31.05.2010','dd.mm.rrrr') and ADD_MONTHS(i_date1, -1); 
    return v_qty; 
    exception when no_data_found then return null; 
end;