with activeOpportunities as ( select spl.stageid, spl.typecode, spl.weightAmt, spl.caseAmt, od.*, stg.statuscode, mfr.unitOfMeasure mfrUnitOfMeasure, spl.availableDate, case when od.SubjectDetailType is not null then od.SubjectDetailType else spl.subjectType end as subjectType, case when od.SubjectDetailType is not null then od.SubjectDetailId else spl.subjectId end as subjectId from tbl_spl_opportunities spl with (nolock) inner join tbl_spl_stages stg with (nolock) on stg.stageid = spl.stageid and stg.ownerid = spl.ownerid and stg.ownertype = spl.ownertype left outer join tbl_SPL_OpportunitySubjectDetails od with (nolock) on od.opportunityid = spl.opportunityid and od.ownerid = spl.ownerid and od.ownertype = spl.ownertype left outer join tblManufacturers mfr with (nolock) on mfr.mfr_id = spl.ownerId on mfr.mfr_id = spl.mfrid where spl.ownerid = and spl.ownertype = and spl.partnerid = and spl.partnertype = and stg.statuscode = 'a' ) select * from activeOpportunities where subjectType = 'prd' and subjectId = where subjectType = 'sku' and subjectId =