2017  Kodetalk | Feedback | Privacy Policy | Terms | About
userimage

Oracle11g ORA-01403 No data found using Select Into

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 12I fail to understand why no data is found when the data check above clearly states otherwise.

userimage

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;

Answer is