CODEWITHSUNDEEP
oracle-databaseplsql
Liton Biswas
Liton Biswas

Reputation: 39

substract one to another value in plsql

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:

Upvotes: 0

Views: 790

Answers (2)

Goran Stefanović
Goran Stefanović

Reputation: 328

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.

Upvotes: 0

MT0
MT0

Reputation: 167972

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

Upvotes: 0

Related Questions