select CODE,NAME,ENG_NAME,CUTOUT_STR
from 
(
    select
        EMP_CODE CODE,
        EMP_LAST_NAME || EMP_FIRST_NAME NAME,
        EMP_ENG_NAME ENG_NAME,
        SUBSTR(REPLACE(EMP_ENG_NAME, ' ', ''), 1,10) CUTOUT_STR
    from
        EMP
    where
        ENG_NAME IS NOT NULL
union all

    select
        EMP_CODE CODE,
        EMP_LAST_NAME || EMP_FIRST_NAME NAME,
        'NULL' ENG_NAME,
        '未入力' CUTOUT_STR
    from
        EMP
    where
        EMP_ENG_NAME IS NULL
) a
order by CUTOUT_STR desc,CODE desc