HI All,

It is late and my brain is not functioning... The below listed query returns the following error message after adding lines of code that are currently commented out:

Subquery returned more than 1 value. This is not permitted when the subquery follows

=, !=, <, <= , >, >=

or when the subquery is used as an expression.

How can I modify this query to prevent this error?

Thank you,
Daniel

Select Distinct prtnum, 
Coalesce((Select curqty from lens_invsum Lens where lens.prtnum = Lens_invsum.prtnum and invsts = 'R' and sitnam ='NJ'), 0) as NJUsable,
Coalesce((Select curqty from lens_invsum Lens where lens.prtnum = Lens_invsum.prtnum and invsts = 'R' and sitnam ='NV'), 0) as NVUsable,
--Coalesce((Select curqty from lens_invsum Lens where lens.prtnum = Lens_invsum.prtnum and invsts <> 'R' and sitnam ='NJ'), 0) as NJNotUseable,
--Coalesce((Select curqty from lens_invsum Lens where lens.prtnum = Lens_invsum.prtnum and invsts <> 'R' and sitnam ='NV'), 0) as NVNotUseable
from Lens_invsum
Order by prtnum