eobiki10 (12/23/2013)
Thanks, Sean earlier pointed it out in an earlier response. However, I have this code from another table which I would want to add as a column to the table. What will be the best way to add it for good performance?
CASE WHEN isnull((SELECT top 1 i.commentsFROMHBA_SOURCE_BUS i
INNER JOINCXA_FOLDER_OBJECT j ON i.FO_UNO = j.FO_UNO
INNER JOINHBM_NAME k ON k.NAME_UNO = j.OBJECT_UNO
INNER JOINHBL_NAME_CLASS h on k.name_class_code = h.name_class_code
WHEREi.MATTER_UNO = m.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 HBA_SOURCE_BUS i
INNER JOIN CXA_FOLDER_OBJECT j ON i.FO_UNO = j.FO_UNO
INNER JOIN HBM_NAME k ON k.NAME_UNO = j.OBJECT_UNO
inner join HBL_NAME_CLASS h on k.name_class_code = h.name_class_code
WHERE i.MATTER_UNO = m.MATTER_UNO and ref_source_code = 'LEN1'), ''), 6, len(isnull
((SELECT top 1 i.comments
FROM HBA_SOURCE_BUS i
INNER JOIN CXA_FOLDER_OBJECT j ON i.FO_UNO = j.FO_UNO
INNER JOIN HBM_NAME k ON k.NAME_UNO = j.OBJECT_UNO
inner join HBL_NAME_CLASS h on k.name_class_code = h.name_class_code
WHERE i.MATTER_UNO = m.MATTER_UNO and ref_source_code = 'LEN1'), '')
)), 'WALK-IN', ''), '|', ''), '£', ''), '¬', ''), ',', '')))
ELSE '0'
END AS lender1_debt
What is the distribution of values in column 'MATTER_UNO' of table HBA_SOURCE_BUS? Are they unique?
Are values in column 'comments' of table HBA_SOURCE_BUS only valid in this context if HBA_SOURCE_BUS joins to the other three tables as shown?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden