بلاول ببر بلاول ببر - 3 months ago 10
SQL Question

wrong calculation in oracle 10g report

We create the two formula column in oracle 10g report builder when we subtract these formula column but answer is wrong

1: first formula column

function sale_formulaFormula return Number is

begin
DECLARE
BALANCE NUMBER;
n number;
BEGIN
SELECT NVL(SUM(PLD.DR),0)-NVL(SUM(PLD.CR),0)
INTO BALANCE
FROM
LEDGER PLD
WHERE
(PLD.d_ID in (9)) AND PLD.LD_DATE BETWEEN :P_FROM_DATE AND :P_TO_DATE;
return balance;
end;
end;





2: second formula column

function expence_formulaFormula return Number is
begin
DECLARE
BALANCE NUMBER;
n number;
BEGIN
SELECT NVL(SUM(PLD.DR),0)-NVL(SUM(PLD.CR),0)
INTO BALANCE
FROM
LEDGER PLD
WHERE
(PLD.d_ID in (10,19,20,5,18)) AND PLD.LD_DATE BETWEEN :P_FROM_DATE AND :P_TO_DATE;
return balance;
end;
end;


When I subtract these formula columns in other formula column but Answer is not True

3:

function CF_4Formula return Char is
begin
return nvl(:sale_formula,0)-nvl(:expence_formula,0);
end;

Answer

To start with your piece of code, i would say the function creation is itself wrong and it would not have complied.

Secondly I followed your steps and check what result i got.

create table LEDGER(d_ID number,dr number, cr number,LD_DATE date);
--------------------------------------------------------------

Insert into LEDGER   (D_ID, DR, CR, LD_DATE)
 Values   (9, 12, 43, TO_DATE('08/30/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into LEDGER   (D_ID, DR, CR, LD_DATE)
 Values  (9, 100, 200, TO_DATE('08/30/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into LEDGER    (D_ID, DR, CR, LD_DATE)
 Values  (18, 899, 786, TO_DATE('08/30/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into LEDGER (D_ID, DR, CR, LD_DATE)
 Values (19, 665, 77, TO_DATE('08/30/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into LEDGER   (D_ID, DR, CR, LD_DATE)
 Values  (9, 354, 35, TO_DATE('08/30/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into LEDGER  (D_ID, DR, CR, LD_DATE)
 Values (5, 43.43, 89.4, TO_DATE('08/30/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into LEDGER (D_ID, DR, CR, LD_DATE)
 Values  (10, 77, 654, TO_DATE('08/30/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into LEDGER (D_ID, DR, CR, LD_DATE)
 Values  (9, 75, 97, TO_DATE('08/30/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into LEDGER  (D_ID, DR, CR, LD_DATE)
 Values  (9, 59.45, 69.3, TO_DATE('08/30/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;

--------------------------------------------------------

--1: first formula column

create or replace function sale_formulaFormula 
return Number is
BALANCE NUMBER;
n number;
BEGIN

   SELECT NVL(SUM(PLD.DR),0)- NVL(SUM(PLD.CR),0)
     INTO BALANCE
   FROM LEDGER PLD
   WHERE (PLD.d_ID in (9)) ;
   --Commenting since am taking only 1 day data
   --AND PLD.LD_DATE BETWEEN :P_FROM_DATE AND :P_TO_DATE; 

   Return balance;

end;

--Call
select sale_formulaFormula from dual; 

--Got result -- 156.15

--------------------------------------------------------------------------------

--2: second formula column
create or replace function expence_formulaFormula 
return Number is
BALANCE NUMBER;
n number;
BEGIN

    SELECT NVL(SUM(PLD.DR),0)-NVL(SUM(PLD.CR),0)
    INTO BALANCE
    FROM  LEDGER PLD
    WHERE (PLD.d_ID in (10,19,20,5,18));
    --Commenting since am taking only 1 day data 
    --AND PLD.LD_DATE BETWEEN :P_FROM_DATE AND :P_TO_DATE;

  Return balance;

end;

------------------------------------------------   
--Call
select expence_formulaFormula from dual;

--Got result  -- 78.03
----------------------------------------------------

--3 Function 3
create or replace function CF_4Formula 
return Char is
begin
  --return type is char so converting the result to char 
  return to_char(nvl(sale_formulaFormula,0) - nvl(expence_formulaFormula,0));
end;


--Call
select CF_4Formula from dual;

Got result --> 156.15 - 78.03 = 78.12 <--> Which is correct..!!!!