SELECT COALESCE(trtr.cr_cr, trtr.fs_cr, trtr.ps_cr) AS real_cr,
(select req_desc from fptspec.t_change_request where cr_number  = real_cr) as descr,
(select req_description from fptspec.tbl_func_spec where func_spec_no = REQ_NO and real_cr = '') as fs_desc,
(select req_description from fptspec.tbl_prog_spec where program_spec_no = REQ_NO and real_cr = '') as ps_desc,
COALESCE(trtr.fs_pernr,trtr.ps_pernr,trtr.cr_pernr) as real_pernr,
COALESCE(trtr.fs_ename,trtr.ps_ename,trtr.cr_ename) as real_ename, trtr.TRRNT, trtr.REQ_NO

FROM
(
SELECT tr.*,
(SELECT pernr FROM ess2.mysql_pa0105 WHERE usrid_long =
UPPER((SELECT email from fptspec.tbl_user
where id = (SELECT creator from fptspec.tbl_func_spec
 where func_spec_no = tr.REQ_NO))) AND (CURDATE() BETWEEN begda AND endda) AND subty IN (0010) LIMIT 1)
as fs_pernr,
(select ename from ess2.mysql_pa0002 where pernr = fs_pernr) as fs_ename,
            (SELECT pernr FROM ess2.mysql_pa0105 WHERE usrid_long = UPPER(
                    (SELECT email from fptspec.tbl_user where id = (SELECT creator from fptspec.tbl_prog_spec where program_spec_no = tr.REQ_NO))) AND (CURDATE() BETWEEN begda AND endda) AND subty IN (0010) LIMIT 1) as ps_pernr,
(select ename from ess2.mysql_pa0002 where pernr = ps_pernr) as ps_ename,
            (SELECT sap_closing FROM fptspec.t_change_request where cr_number = tr.REQ_NO) as cr_pernr,
(select ename from ess2.mysql_pa0002 where pernr = cr_pernr) as cr_ename,
(select cr_number from fptspec.t_change_request where cr_number = tr.REQ_NO) as cr_cr,
(select cr_number from fptspec.tbl_func_spec where func_spec_no = tr.REQ_NO) as fs_cr,
(select cr_number from fptspec.tbl_prog_spec where program_spec_no  = tr.REQ_NO) as ps_cr,
(select req_desc from fptspec.t_change_request where cr_number  = tr.REQ_NO) as cr_desc
FROM ZTBINNOV003 as tr order by cr_cr desc,fs_cr desc,ps_cr desc
) AS trtr