SELECT DISTINCT nm.code_type code_type, nm.code code, ECD.ACTIVE, ( CASE WHEN ecr.child_code_type IS NOT NULL AND ecr.child_code IS NOT NULL THEN eca.description END || '' || CASE WHEN ecr.child_code_type IS NULL OR ecr.child_code IS NULL THEN ecd.description END ) AS descr, eca.active, ecr.child_code_type, ecr.child_code, eca.description ans_descr, ( CASE WHEN ecr.child_code_type IS NOT NULL AND ecr.child_code IS NOT NULL THEN eca.sort_order END || '' || CASE WHEN ecr.child_code_type IS NULL OR ecr.child_code IS NULL THEN ecd.sort_order END ) AS sort_order, ( CASE WHEN ecr.child_code_type IS NOT NULL AND ecr.child_code IS NOT NULL THEN eca.exclusive_flg END || '' || CASE WHEN ecr.child_code_type IS NULL OR ecr.child_code IS NULL THEN ecd.exclusive_flg END ) AS exclusive_flg, nm.nibrs_code nm_nibrs_code, ques.nibrs_code ques_nibrs_code FROM nibrs_tab_cols nt INNER JOIN nibrs_code_ejs_mappings nm ON nm.code_type = nt.code INNER JOIN ejs_codes ecd ON ecd.code_type = nm.code_type AND ecd.code = nm.code INNER JOIN offense_reqques_vw ques ON ques.code_type = ecd.code_type LEFT OUTER JOIN ejs_codes_relations ecr ON ecr.parent_code_type = nm.code_type AND ecr.parent_code = nm.code LEFT OUTER JOIN ejs_codes eca ON eca.code_type = ecr.child_code_type AND eca.code = ecr.child_code WHERE nt.inc_section_code = 'OFFENSES'