DelphiFAQ Home Search:
General :: Databases :: InterBase
Help with InterBase, Borland's open source database.

Articles:

This list is sorted by recent document popularity (not total page views).
New documents will first appear at the bottom.

Featured Article

SQL error message 'Multiple rows in a singleton select'

Question:

I need to know how to resolve an exception that says 'Multiple rows in a singleton select'

Answer:

This error message happens when you have a select statement (in a stored procedure) that you expected to return one row but (because of erronous data?) it returns multiple rows.

E.g.
select customer_name
from customers
where cust_id = 1234
into :c_name;


This works fine until you have two records with cust_id = 1234.
If you are in a situation like me (a project with 150 stored procedures totalling 40,000 lines of brainless SQL) then this may be hard to find. Once you know which stored procedure, you can try changing your statements by putting them temporarily into FOR loops:

for
   select customer_name
   from customers 
   where cust_id = 1234
   into :c_name
   do begin
     some_dummy_statement; /* maybe log here? */
   end;

Generated 8:00:32 on Apr 25, 2017