Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

How to use the subquery column highglighted in the GROUP BY Expand / Collapse
Author
Message
Posted Monday, December 23, 2013 7:51 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, November 3, 2014 8:34 AM
Points: 85, Visits: 346
Thanks Chris. Your suggestion works perfectly fine. The APPLY is really the magic wow!!!

EO
Post #1525545
Posted Monday, December 23, 2013 8:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 6,872, Visits: 14,185
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
Post #1525556
Posted Monday, December 23, 2013 10:32 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, November 3, 2014 8:34 AM
Points: 85, Visits: 346
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

Post #1525597
Posted Monday, December 30, 2013 2:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 6,872, Visits: 14,185
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
Post #1526424
Posted Monday, December 30, 2013 11:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, November 3, 2014 8:34 AM
Points: 85, Visits: 346
Thanks Chris for responding to this query. I have been able to solve the problem.

EO
Post #1526564
Posted Tuesday, December 31, 2013 1:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 6,872, Visits: 14,185
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
Post #1526687
Posted Thursday, January 2, 2014 10:38 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, November 3, 2014 8:34 AM
Points: 85, Visits: 346
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
Post #1527222
Posted Thursday, January 2, 2014 11:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:29 AM
Points: 1,037, Visits: 6,969
-- 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
Post #1527235
Posted Thursday, January 2, 2014 1:25 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, November 3, 2014 8:34 AM
Points: 85, Visits: 346
Thanks chris. I will check out your solution. You and Sean have been so helpful.

E.O
Post #1527269
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse