How to use the subquery column highglighted in the GROUP BY

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  December 31, 2013 at 1:55 am

  #1676535

  eobiki10 (12/30/2013)

  ---

  Thanks Chris for responding to this query. I have been able to solve the problem.

  EO

  Can you post your solution please? It provides closure for this thread and for those who have taken the time to provide advice, and may help folks who stumble upon it.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  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

• eobiki10

  SSCrazy

  Points: 2390

  More actions

  January 2, 2014 at 10:38 am

  #1677000

  I made it a CTE as in below:

  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

  Then I have a column from the select as follows ct.comments AS Lender1_Debt

  E.O

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  January 2, 2014 at 11:33 am

  #1677011

  -- 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!

  ---

  [font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

  For better assistance in answering your questions, please read this[/url].

  ---

  Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

  Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

• eobiki10

  SSCrazy

  Points: 2390

  More actions

  January 2, 2014 at 1:25 pm

  #1677044

  Thanks chris. I will check out your solution. You and Sean have been so helpful.

  E.O