Calculate sum of 2 variables from different cursors - Pl/sql function

I am relatively new to pl/sql and coding in general and appreciate your help. Have 2 tables as follows. Writing a function to calculate the sum(amt1)+ sum(amt2) for a date range.

I cant link table1 and table2 directly as it gives bad results. I have a cursor to calculate sum(amt1) from table1 and another cursor to calculate sum(amt2) from table2. How do i calculate sum(amt1)+ sum(amt2) from these 2 cursors

Table 1

Id  Amt Date
1   342 21-May-02
2   421 30-Mar-01
3   598 4-May-11
2   843 14-Jun-12
4   457 21-May-09
1   2346    24-Apr-98

Table 2


Id  Amt2    Date2
1   342 21-May-02
2   421 30-Mar-01
3   598 4-May-11
2   843 14-Jun-12
4   457 21-May-09
1   2346    24-Apr-98

this is my function which got compiled but when I am testing it, it doesnt return any value

CREATE OR REPLACE FUNCTION FZ_HH_BY_DATE_COMMITMENT (pidm          number,
                                                     start_date    date,
                                                     end_date      date,
                                                     desg          varchar2)
   RETURN number
AS
     total_commit   number(13,2) := 0;
   total_gifts    number(13,2) := 0;
   total_pledges  number(13,2) := 0;
   sp_pidm        number (9);

  CURSOR date_commit_gifts IS
  SELECT SUM(azvglst_amt) FROM acu.azvglst
  WHERE  azvglst_pidm IN (pidm, sp_pidm)
  AND SUBSTR (azvglst_desg, 0, LENGTH (desg)) = desg
  AND azvglst_gift_date BETWEEN start_date AND end_date
  AND azvglst_pledge_no = '0000000'
  AND (azvglst_pgve_code <> '3P' OR azvglst_pgve_code IS NULL);


  CURSOR all_date_commit_gifts IS
  SELECT SUM(azvglst_amt) FROM acu.azvglst
  WHERE  azvglst_pidm IN (pidm, sp_pidm)
  AND azvglst_gift_date BETWEEN start_date AND end_date
  AND azvglst_pledge_no = '0000000'
  AND (azvglst_pgve_code <> '3P' OR azvglst_pgve_code IS NULL);


   CURSOR date_commit_pledges
   IS
      SELECT   SUM (agvplst_amt_pledged)
              FROM   a开发者_如何转开发gvplst
       WHERE   agvplst_pledge_date BETWEEN start_date AND end_date
               AND SUBSTR (agvplst_desg, 0, LENGTH (desg)) = desg
               AND agvplst_pidm IN (pidm, sp_pidm)
               AND agvplst_psta_code NOT IN ('I','C','U');

   CURSOR all_date_commit_pledges
   IS
      SELECT   SUM (agvplst_amt_pledged)
              FROM   agvplst
       WHERE   agvplst_pledge_date BETWEEN start_date AND end_date
                              AND agvplst_pidm IN (pidm, sp_pidm)
                              AND agvplst_psta_code NOT IN ('I','C','U');
BEGIN
   sp_pidm := TO_NUMBER (fz_split_fields (fz_spouse_info (pidm), 1));

   IF desg IS NULL
   THEN
      OPEN all_date_commit_gifts;
      FETCH all_date_commit_gifts INTO   total_gifts;
      CLOSE all_date_commit_gifts;

      OPEN all_date_commit_pledges;
      FETCH all_date_commit_pledges INTO total_pledges;
      CLOSE all_date_commit_pledges;

      ELSE OPEN date_commit_gifts;
      FETCH date_commit_gifts INTO   total_gifts;
      CLOSE date_commit_gifts;

      OPEN date_commit_pledges;
      FETCH date_commit_pledges INTO total_pledges;
      CLOSE date_commit_pledges;
      END IF;
      total_commit := total_gifts + total_pledges;
      RETURN total_commit;

END;

---

UPDATED:

CREATE OR REPLACE FUNCTION CALC_BY_DATE(
    start_date DATE,
    end_date DATE)
    RETURN NUMBER
IS
  sum_amt NUMBER;
BEGIN
  SELECT SUM(AMT)
  INTO   sum_amt
  FROM   (
         SELECT AMT
         FROM   TABLE1
         WHERE  DATE >= start_date
         AND    DATE < end_date + 1
         UNION ALL
         SELECT AMT
         FROM   TABLE2
         WHERE  DATE >= start_date
         AND    DATE < end_date + 1
         );

  RETURN(NVL(sum_amt, 0));
END;

By the way, it's ok if you just use select... into instead of open cursor, fetch, close.

It was a looong time ago when that made any difference regarding query performance.

ONE MORE THING

Most likely problem is the line:

total_commit := total_gifts + total_pledges

you should use:

total_commit := NVL(total_gifts, 0) + NVL(total_pledges, 0)

Remember if you sum whatever to NULL you ALLWAYS get NULL.

Best regards.

---

Without needing two cursors you could do:

select id, sum(amt) from
( select id, amt1 as amt from table1
  union all
  select id, amt2 as amt from table2
)
group by id