SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to use the subquery column highglighted in the GROUP BY


How to use the subquery column highglighted in the GROUP BY

Author
Message
eobiki10
eobiki10
SSC-Enthusiastic
SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)

Group: General Forum Members
Points: 181 Visits: 369
Thanks Chris. Your suggestion works perfectly fine. The APPLY is really the magic wow!!!

EO
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15988 Visits: 19524
eobiki10 (12/23/2013)
Thanks Chris. Your suggestion works perfectly fine. The APPLY is really the magic wow!!!

EO


Excellent. There's a lesson here - know your data. If you knew in advance of writing your query that most of your tables join together as one-to-one, you would have figured it out.
APPLY isn't key here - you could have done the same job with a joined derived table. Similar to the APPLY block but you return the key column in the subquery - in addition to the aggregated columns - then use it in the join.

You should investigate this:
isnull((Select top 1 'Y'
from bo_live5.dbo.hba_source_bus AS ii
where ii.matter_uno=i.matter_uno and ii.comments LIKE '%walk-in%'),'N') AS [Walk In]

It doesn't make sense.

“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
Exploring Recursive CTEs by Example Dwain Camps
eobiki10
eobiki10
SSC-Enthusiastic
SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)

Group: General Forum Members
Points: 181 Visits: 369
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.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'), '') 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


ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15988 Visits: 19524
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.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'), '') 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?

“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
Exploring Recursive CTEs by Example Dwain Camps
eobiki10
eobiki10
SSC-Enthusiastic
SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)

Group: General Forum Members
Points: 181 Visits: 369
Thanks Chris for responding to this query. I have been able to solve the problem.

EO
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15988 Visits: 19524
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
Exploring Recursive CTEs by Example Dwain Camps
eobiki10
eobiki10
SSC-Enthusiastic
SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)

Group: General Forum Members
Points: 181 Visits: 369
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
ChrisM@home
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2020 Visits: 10341
-- 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!




Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




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
eobiki10
SSC-Enthusiastic
SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)

Group: General Forum Members
Points: 181 Visits: 369
Thanks chris. I will check out your solution. You and Sean have been so helpful.

E.O
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search