CODEWITHSUNDEEP
oracle-databasefor-loopplsqlora-01422
Olaide
Olaide

Reputation: 1

For loop in Oracle sql for calculating transaction balance. ORA_01422: exact fetch returns more than requested number of row

I am trying to write a For loop program which will calculate total balance for each account. But I am getting this error

ORA_01422: exact fetch returns more than requested number of row

Below is my script

DECLARE
v_area          VARCHAR2(3);
v_account_no   VARCHAR2(20);
v_cur        VARCHAR2(3);
v_local_bal    NUMBER;
v_fo_bal    NUMBER;

BEGIN
  FOR y IN (SELECT area,cur,account_no FROM accounts 
    WHERE account_no IN ('1123456879','2222222222','3333333333','4444444444','5555555555','6666666666','7777777777'))
    LOOP
      SELECT area,accno,cur,SUM(DECODE(drcr, 'C', local_amt, -local_amt)) local_bal,NVL(SUM(DECODE(drcr, 'C', fo_amt, -fo_amt)), 0) fo_bal
      INTO v_area, v_account_no, v_cur, v_local_bal, v_fo_bal
      FROM transactions
      WHERE acc_number = y.account_no
      AND transdate <= '29Dec2023'
      AND area = '234'
      GROUP BY area, acc_number, cur;

    -- Display individual account balances
    DBMS_OUTPUT.PUT_LINE('Account: ' || y.account_no|| ', Area: ' || v_area || ', Curr: ' || v_cur);
    DBMS_OUTPUT.PUT_LINE('Local Balance: ' || v_local_bal);
    DBMS_OUTPUT.PUT_LINE('FO Balance: ' || v_fo_bal);

   
END LOOP;

END;/

Here is the output I am expecting

Account: 1123456879, Area: 234 , Curr: XYX, Local Balance: 2000 FO Balance: 500
Account: 2222222222, Area: 234 , Curr: XXX, Local Balance: 1000 FO Balance: 0
Account: 3333333333, Area: 234 , Curr: XXX, Local Balance: 1000 FO Balance: 0

Upvotes: 0

Views: 56

Answers (1)

Littlefoot
Littlefoot

Reputation: 143163

Query is invalid and doesn't even have a chance of returning that error because you're

  • selecting area, accno, cur
  • and grouping by area, acc_number, cur

Once that's fixed, then it might return too many rows.

Let's presume that you actually wanted to select acc_number. Then

This:

SELECT area,acc_number,cur,SUM(DECODE(drcr, 'C', local_amt, -local_amt)) local_bal,
       NVL(SUM(DECODE(drcr, 'C', fo_amt, -fo_amt)), 0) fo_bal
  INTO v_area, v_account_no, v_cur, v_local_bal, v_fo_bal
  FROM transactions
  WHERE acc_number = y.account_no
  AND transdate <= '29Dec2023'
  AND area = '234'
  GROUP BY area, acc_number, cur;

doesn't return a single row for each y.account_no.

  • area should be OK, it is a constant (234)
  • therefore, acc_number and cur remain suspicious

What to do? Depends on what you want; you can aggregate them as well, for example

  SELECT area,
         LISTAGG (acc_number, ', ') WITHIN GROUP (ORDER BY acc_number) AS accno,
         LISTAGG (cur, ', ') WITHIN GROUP (ORDER BY cur) AS cur,
         SUM (DECODE (drcr, 'C', local_amt, -local_amt)) local_bal,
         NVL (SUM (DECODE (drcr, 'C', fo_amt, -fo_amt)), 0) fo_bal
    INTO v_area,
         v_account_no,
         v_cur,
         v_local_bal,
         v_fo_bal
    FROM transactions
   WHERE     acc_number = y.account_no
         AND transdate <= '29Dec2023'
         AND area = '234'
GROUP BY area;

Or, you can use loop within a loop and display several rows for each y.account_no (but that's not what your expected output suggests).

Besides, what is transactions.transdate column's datatype? Query you posted suggests it is a string (varchar2). Should be date, which is what value (23Dec2023) suggests. If you're relying on implicit datatype conversion and NLS settings, don't. A better option would be e.g.

and transdate <= date '2023-12-29'

Upvotes: 1

Related Questions