Multiple rows in singleton select

This error shows up when you have some SQL statement that tries to evaluate a single value against a multi-row select. Here's an example:

select *
from EMPLOYEE e
where e.EMP_NO = (select p.EMP_NO from EMPLOYEE_PROJECT p);

This won't work as the subselect returns multiple p.EMP_NO values and Firebird can't decide which one to use. This would happen even if all the values are the same. You need to decide what exactly you want to do and rewrite the query. For example, if I wanted to get any employee that has a project:

select *
from EMPLOYEE e
where e.EMP_NO = (select first 1 p.EMP_NO from EMPLOYEE_PROJECT p);

If I wanted all employees that have some project:

select *
from EMPLOYEE e
where e.EMP_NO IN (select p.EMP_NO from EMPLOYEE_PROJECT p);

Or, more efficiently:

select *
from EMPLOYEE e
where exists (select 1 from EMPLOYEE_PROJECT p where e.EMP_NO = p.EMP_NO);

Or, even better:

select e.*
from EMPLOYEE e
join EMPLOYEE_PROJECT p ON e.EMP_NO = p.EMP_NO;

If I wanted employees of a particular project:

select *
from EMPLOYEE e
where e.EMP_NO in (select p.EMP_NO from EMPLOYEE_PROJECT p where p.PROJ_ID = 8);

Or, more efficiently:

select *
from EMPLOYEE e
where exists (select 1 from EMPLOYEE_PROJECT p where p.PROJ_ID = 8 and p.EMP_NO = e.EMP_NO);

Which can also be written as:

select e.*
from EMPLOYEE e
join EMPLOYEE_PROJECT p on p.EMP_NO = e.EMP_NO
where p.PROJ_ID = 8;