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

select distinct PrtNum, ISNULL(c1.CurQty,0) as NJUsable, ISNULL(c2.CurQty,0) as NVUsable from Lens_InvSum L ;
LEFT JOIN (Select PrtNum, curqty from lens_invsum Lens where lens.prtnum = Lens_invsum.prtnum and invsts = 'R' and sitnam ='NJ') c1 on
L.PrtNum = c1.PrtNum LEFT JOIN (...) C2

In other words, use derived tables instead.