select
c.co_dn,
Unit_Id_Code,
Unit_Serial_Nbr,
Unit_Unit_Type,
c_fff.fff_stn_8,
c_fff.fff_std_7
from C_BTH_UNIT t
join ctp_common_objects c
on c.co_gid = t.obj_gid
join ctp_common_objects fff
on fff.co_dn =
REGEXP_SUBSTR(c.co_dn, 'SXZ/SXZ-(\d+)/ZXC-(\d+)', 1, 1)
join c_fff_fff c_fff
on c_fff.obj_gid = fff.co_gid
and c_fff.conf_id = 1
where UNIT_UNIT_TYPE not in ('AAA',
'BBB',
'CCC',
'DDD',
'EEE',
'CCC',
'VVV',
)
and t.conf_id = 1