select cho.cho_id, case when cho.cho_type = 'O' then 'HQ' when cho.cho_type = 'G' then 'GPO' when cho.cho_type = 'C' then 'CMC' when cho.cho_type = 'H' then 'Holding' when cho.cho_type = 'S' then 'Special/Host' else cho.cho_type end as cho_type, cho.cho_name, cho.cho_address, opr.fsltablecode, opr.ownerId, opr.operatorId, case when opr.oprcompanyType = '0' then 'Unit/Independent' when opr.oprCompanyType = '1' then 'HQ' when opr.oprCompanyType = '2' then 'Holding' when opr.oprCompanyType = 'G' then 'GPO' when opr.oprCompanyType = 'C' then 'CMC' else opr.oprCompanyType end as oprCompanyType, opr.companyName, opr.address, opr.recordSource, opr.fsl_choIdStatus from tbl_OPR_CLientOperators opr with (nolock) inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId and cho.cho_type in ( 'O', 'C', 'G', 'H', 'S' ) where opr.oprCompanyType = '0' and opr.fsltablecode <> 'BRO' -- and opr.fsl_choIdStatus in ( 'A', 'I' ) order by cho.cho_Id