-- a little reformatting shows that the same query is referenced THREE TIMES

CTE (comments, matter_uno) AS

(SELECT case when isnull((

SELECT top 1 i.comments

FROM BO_LIVE3.dbo.HBA_SOURCE_BUS i

INNER JOIN BO_LIVE3.dbo.CXA_FOLDER_OBJECT j ON i.FO_UNO = j.FO_UNO

INNER JOIN BO_LIVE3.dbo.HBM_NAME k ON k.NAME_UNO = j.OBJECT_UNO

inner join BO_LIVE3.dbo.HBL_NAME_CLASS h on k.name_class_code = h.name_class_code

WHERE i.MATTER_UNO = a.MATTER_UNO and ref_source_code = 'LEN1'

), '') like '%DEBT:%'

then

ltrim(rtrim(replace(replace(replace(replace(replace(substring(

isnull((

SELECT top 1 i.comments

FROM BO_LIVE3.dbo.HBA_SOURCE_BUS i

INNER JOIN BO_LIVE3.dbo.CXA_FOLDER_OBJECT j ON i.FO_UNO = j.FO_UNO

INNER JOIN BO_LIVE3.dbo.HBM_NAME k ON k.NAME_UNO = j.OBJECT_UNO

inner join BO_LIVE3.dbo.HBL_NAME_CLASS h on k.name_class_code = h.name_class_code

WHERE i.MATTER_UNO = a.MATTER_UNO and ref_source_code = 'LEN1'

), '')

, 6, len(

isnull((

SELECT top 1 i.comments

FROM BO_LIVE3.dbo.HBA_SOURCE_BUS i

INNER JOIN BO_LIVE3.dbo.CXA_FOLDER_OBJECT j ON i.FO_UNO = j.FO_UNO

INNER JOIN BO_LIVE3.dbo.HBM_NAME k ON k.NAME_UNO = j.OBJECT_UNO

inner join BO_LIVE3.dbo.HBL_NAME_CLASS h on k.name_class_code = h.name_class_code

WHERE i.MATTER_UNO = a.MATTER_UNO and ref_source_code = 'LEN1'

), '')

)), 'WALK-IN', ''), '|', ''), '£', ''), '¬', ''), ',', '')))

else

'0'

end , a.matter_uno

FROM BO_LIVE3.dbo.hbm_matter a

-- which can be rewritten as

CTE (comments, matter_uno) AS (

SELECT case

when x.comments like '%DEBT:%' then

ltrim(rtrim(

replace(replace(replace(replace(replace(

substring(x.comments, 6, len(x.comments))

, 'WALK-IN', ''), '|', ''), '£', ''), '¬', ''), ',', '')

))

else '0' end,

a.matter_uno

FROM BO_LIVE3.dbo.hbm_matter a

CROSS APPLY (

SELECT top 1

comments = ISNULL(i.comments,'')

FROM BO_LIVE3.dbo.HBA_SOURCE_BUS i

INNER JOIN BO_LIVE3.dbo.CXA_FOLDER_OBJECT j ON i.FO_UNO = j.FO_UNO

INNER JOIN BO_LIVE3.dbo.HBM_NAME k ON k.NAME_UNO = j.OBJECT_UNO

inner join BO_LIVE3.dbo.HBL_NAME_CLASS h on k.name_class_code = h.name_class_code

WHERE i.MATTER_UNO = a.MATTER_UNO and ref_source_code = 'LEN1'

) x

)

-- HBA_SOURCE_BUS is referenced in your main query - do you really need this?

-- Include HBA_SOURCE_BUS.comments from your main query and see if it differs from CTE.comments - it's

-- possible and may be important. Find out!