بلاول ببر - 1 year ago 63
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;
``````

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