declare @ownerId int = ; declare @ownerType varchar(3) = ; with data as ( select distinct ps.sourceOther as source, ps.ownerId as mfr_id from tbl_OPR_ProductStatus ps with (nolock) where ps.sourceType = 'O' and nullif( ps.sourceOther, '' ) is not null and ps.purchasingStatus = 'N' and ps.ownerId = @ownerId and ps.ownerType = @ownerType ) #forJSON( 'data', attributes.dataStore, 'source' )# declare @ownerId int = ; declare @ownerType varchar(3) = ; with rawData as ( select ps.sourceOther as source, ph.mfr_id as mfr_id from tbl_OPR_ProductStatus ps with (nolock) inner join tbl_DW_ProductHierarchyPaths#_tableSuffix# ph with (nolock) on ph.ownerId = ps.ownerId and ph.ownerType = ps.ownerType and ( ph.catalogType = 'SKU' and ph.catalogId = ps.skuId ) where ps.sourceType = 'O' and nullif( ps.sourceOther, '' ) is not null and ps.purchasingStatus = 'N' and ps.ownerId = @ownerId and ps.ownerType = @ownerType union all select ps.sourceOther as source, ph.mfr_id as mfr_id from tbl_OPR_ProductStatus ps with (nolock) inner join tbl_DW_ProductHierarchyPaths#_tableSuffix# ph with (nolock) on ph.ownerId = ps.ownerId and ph.ownerType = ps.ownerType and ( ph.catalogType = 'PRD' and ph.catalogId = ps.prodId and nullif(ps.skuId, 0 ) is null ) where ps.sourceType = 'O' and nullif( ps.sourceOther, '' ) is not null and ps.purchasingStatus = 'N' and ps.ownerId = @ownerId and ps.ownerType = @ownerType ) , data as ( select distinct source, mfr_id from rawData ) #forJSON( 'data', attributes.dataStore, 'source' )#