=, !=, <, <= , >, >=or when the subquery is used as an expression.
>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 ;