>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
>

It is not clear what you wanrt to do. Not sure if that might be:

Select distinct prtNum,
(Select coalesce(min(curqty),0)
from lens_invsum lens 
where lens.prtNum = lens_invsum.prtNum and invsts = 'R' and sitnam ='NJ')  as NJUsable
(Select coalesce(min(curqty),0)
from lens_invsum lens 
where lens.prtNum = lens_invsum.prtNum and invsts = 'R' and sitnam ='NV')  as NVUsable
from Lens_invsum
Order by prtnum

Cetin