Oracle11g ORA-01403 No data found using Select Into

Question

I am quite new to Oracle and I have an issue I have been struggelig With for some hours.

sample:

 Create Table Accounts (Id number(10),Balance number(16,3), Status Varchar2(50),Owner_Id number(10));
 Create Table Transactions (Id number(10),Amount number(16,3), Trxn_date date, Account_Id number(10));
 Create Table Owner (Id number(10), Firstname varchar2(50),Lastname varchar2(50));

 Insert Into Accounts(Id,Balance,Status,Owner_Id) Values (1,1000,'OPEN',10);
 Insert Into Accounts(Id,Balance,Status,Owner_Id) Values (2,5000,'CLOSED',11);
 Insert Into Accounts(Id,Balance,Status,Owner_Id) Values (3,1000,'OPEN',12);
 Insert Into Accounts(Id,Balance,Status,Owner_Id) Values (4,5000,'CLOSED',13);
 Insert Into Accounts(Id,Balance,Status,Owner_Id) Values (5,1000,'OPEN',14);
 Insert Into Accounts(Id,Balance,Status,Owner_Id) Values (6,5000,'CLOSED',15);
 Insert Into Accounts(Id,Balance,Status,Owner_Id) Values (7,1000,'OPEN',16);
 Insert Into Accounts(Id,Balance,Status,Owner_Id) Values (8,5000,'CLOSED',17);
 Insert Into Accounts(Id,Balance,Status,Owner_Id) Values (9,1000,'OPEN',18);
 Insert Into Accounts(Id,Balance,Status,Owner_Id) Values (10,5000,'CLOSED',19);
 Insert Into Accounts(Id,Balance,Status,Owner_Id) Values (11,1000,'OPEN',20);
 Insert Into Accounts(Id,Balance,Status,Owner_Id) Values (12,5000,'CLOSED',21);

 Insert Into Owner(Id,Firstname,Lastname) Values (10,'John','TEST1');
 Insert Into Owner(Id,Firstname,Lastname) Values (11,'John','TEST2');
 Insert Into Owner(Id,Firstname,Lastname) Values (10,'John','TEST3');
 Insert Into Owner(Id,Firstname,Lastname) Values (11,'John','TEST4');
 Insert Into Owner(Id,Firstname,Lastname) Values (10,'John','TEST5');
 Insert Into Owner(Id,Firstname,Lastname) Values (11,'John','TEST6');
 Insert Into Owner(Id,Firstname,Lastname) Values (10,'John','TEST7');
 Insert Into Owner(Id,Firstname,Lastname) Values (11,'John','TEST8');
 Insert Into Owner(Id,Firstname,Lastname) Values (10,'John','TEST9');
 Insert Into Owner(Id,Firstname,Lastname) Values (11,'John','TEST10');
 Insert Into Owner(Id,Firstname,Lastname) Values (10,'John','TEST11');
 Insert Into Owner(Id,Firstname,Lastname) Values (11,'John','TEST12');

 Insert Into Transactions(Id,Amount,Trxn_Date,Account_Id) Values (1,10,'02-FEB-2015',5);
 Insert Into Transactions(Id,Amount,Trxn_Date,Account_Id) Values (2,10,'02-APR-2015',5);
 Insert Into Transactions(Id,Amount,Trxn_Date,Account_Id) Values (3,10,'02-JUN-2015',5);
 Insert Into Transactions(Id,Amount,Trxn_Date,Account_Id) Values (4,10,'02-AUG-2015',5);
 Insert Into Transactions(Id,Amount,Trxn_Date,Account_Id) Values (5,10,'02-FEB-2015',2);
 Insert Into Transactions(Id,Amount,Trxn_Date,Account_Id) Values (6,10,'02-APR-2015',2);
 Insert Into Transactions(Id,Amount,Trxn_Date,Account_Id) Values (7,10,'02-JUN-2015',2);
 Insert Into Transactions(Id,Amount,Trxn_Date,Account_Id) Values (8,10,'02-AUG-2015',2);     

Data Check:

   Select Unique(Account_Id) From Accounts A
 Inner Join Owner B on B.ID=A.OWNER_ID 
 Inner Join Transactions I on I.ACCOUNT_ID=A.Id
   Where I.Trxn_date Between '01-FEB-2015' and '01-JUL-2015' 
 And A.Status='CLOSED'
 and A.Balance=5000;/*1 Row Returned*/

The Loop must exit at first Id returned

   Declare
   l_NewDate date:='01-FEB-2015';
   l_OldDate date:='01-JUL-2015';
   l_pID number(10);

 Begin
   For I in (Select Account_Id From Transactions 
   Where Trxn_date Between l_NewDate and l_OldDate)

   Loop

   Select Id Into l_pID From 
   (Select B.Id From Accounts A 
   Inner Join Owner B on A.Owner_Id = B.Id
   Where A.Status = 'CLOSED' And A.Balance = 5000 And A.Id=I.Account_Id)
   Where rownum < 2;
   dbms_output.put_line(l_pID);
   Exit;

   End Loop;
   End;

 ORA-01403: No data found

 ORA-06512: at line 12

I fail to understand why no data is found when the data check above clearly states otherwise.

Regards J. Olsen

Answer 1

Like you say, your data check query:

Select Unique(Account_Id)
  From Accounts A
 Inner Join Owner B on B.ID=A.OWNER_ID 
 Inner Join Transactions I on I.ACCOUNT_ID=A.Id
 Where I.Trxn_date Between '01-FEB-2015' and '01-JUL-2015' 
   And A.Status='CLOSED'
   and A.Balance=5000;

... returns a single row with a single Account_Id value of 2.

But then, your PL/SQL code basically splits the logic in 2 queries. The query you loop on is:

Select Account_Id
  From Transactions 
 Where Trxn_date Between '01-FEB-2015' and '01-JUL-2015'

And, when I run it, it returns:

5
5
5
2
2
2

Now the above's order is not guaranteed, as you don't have an ORDER BY clause. But if you get the results in the same order as me, then your first loop iteration will execute the next query using 5 as input:

Select *
  From Accounts A 
 Inner Join Owner B on A.Owner_Id = B.Id
 Where A.Status = 'CLOSED'
   And A.Balance = 5000
   And A.Id = 5

... which doesn't return any data, which is why you get your error.

If you would have been lucky enough to have started with the value of 2:

Select *
  From Accounts A 
 Inner Join Owner B on A.Owner_Id = B.Id
 Where A.Status = 'CLOSED'
   And A.Balance = 5000
   And A.Id = 2

... it would have worked as expected.

I wish I could recommend a proper solution, but I just don't truly understand what you are trying to do. But it certainly feels like you shouldn't need PL/SQL loops to do what you want. A simple query should be sufficient. Your data check query seems like a good start.

EDIT

For what it's worth, I think this is a more straight forward way of doing the exact same thing you are intending to do (no loops):

Declare
   l_NewDate date:='01-FEB-2015';
   l_OldDate date:='01-JUL-2015';
   l_pID number(10);
 Begin
   select o.id into l_pID
    from transactions t
    join accounts a
      on a.id = t.account_id
     and a.status = 'CLOSED'
     and a.balance = 5000
    join owner o
      on o.id = a.owner_id
   where t.trxn_date between l_NewDate and l_OldDate
     and rownum < 2;

  dbms_output.put_line(l_pID);
End;