substract one to another value in plsql

0
votes

I want to (Add p.L_VALUE AND p.I_VALUE And with this result, Subtract c.con_value) subtract one column to another column in different table, the function below compiles successfully but the subtract result is not correct:

  • 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;

2
oracleplsql
I think you are missing a ; after the SELECT statement (before the return statement) – a_horse_with_no_name
I see below problems: 1. Since you are not putting any join condition, this will be case of cross join. (10 record in raw_purchase and 20 record in raw_consum will produce 200 result set with all possible combination). 2. Syntactically, you are missing ';' before 'return v_qty;' 3. What if this select query is returning multiple record result? Would you be ablle to store it in V_QTY (which you have defined as number)? – Himanshu
You do not need no_data_found, your query cannot raise that exception. @Himanashu: query cannot return more than one row since it uses aggregate function sum on all three columns, without using group by. – Goran Stefanović
You've changed the question, now it is very different than what you originally asked for. With the changes you have made - what result would you expect if table raw_purchase does not contain any row for item code and time period you provided? Should you sum values of column con_qty from table raw_consum nevertheless? I think you have to understand what your current code is working before fixing anything. Is it clear to you what exactly is the problem with it? – Goran Stefanović

2 Answers

0
votes

Sum the values for the individual tables and then cross join them (rather than cross joining them and then summing which will create duplicate values). You were also missing a semi-colon after the query.

FUNCTION CF_OP_VALFormula RETURN NUMBER
IS
  V_QTY NUMBER(13);
BEGIN
  SELECT p.total - c.total
  INTO   V_QTY
  FROM   ( SELECT sum(nvl(L_VALUE, 0)) + sum(nvl(I_VALUE, 0)) AS total
           FROM   raw_purchase ) p
         CROSS JOIN
         ( SELECT sum(nvl(c.CON_VALUE, 0)) AS total
           FROM   raw_consum ) c;
  RETURN v_qty; 
END;
/
0
votes

One solution would be to use subqueries to calculate sums of columns for every table separately:

function CF_OP_VALFormula return number is
  v_qty number(13);
begin
  select (select nvl(sum(l_value), 0) + nvl(sum(i_value), 0) from raw_purchase) -
         (select nvl(sum(con_value), 0) from raw_consum)
    into v_qty
    from dual;

  return v_qty; 
end;

Note: you do not need to use nvl on every single row - you can use it after calculating sum. This will speed things up a bit.