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

Merging rows Expand / Collapse
Author
Message
Posted Friday, October 5, 2012 7:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
Sean Lange (10/5/2012)
Still nothing to test with but I think this might be close to what you want...

select A, b, MAX(C), MAX(D), MAX(E), MAX(F), g
from
(
SELECT distinct
BASE1.AssetID AS a
,BASE1.Name As b
,case when BASE2.AssetID like 'AST%' then 'Asset Records' else '' end as c
,replace(RELA.Name,char(10),'') As d
,BASE2.AssetID AS e
,BASE2.Name AS f
,BASE1.Company AS g
FROM
BMC_CORE_BMC_BaseElement BASE1 (NOLOCK)
LEFT JOIN BMC_CORE_BMC_BaseRelationship RELA (NOLOCK)
ON BASE1.InstanceId = RELA.Source_InstanceId
LEFT JOIN BMC_CORE_BMC_BaseElement BASE2 (NOLOCK)
ON RELA.Destination_InstanceId = BASE2.InstanceId AND Base2.DatasetId='BMC.ASSET'
LEFT JOIN AST_CMDB_Associations AST (NOLOCK)
ON AST.request_id02 = BASE1.ReconciliationIdentity
and AST.Request_ID02 != '0' and AST.Request_ID01 != '0'
and AST.form_name01 <> 'TMS:TASK' and AST.form_name01 <> 'WOI:WorkOrder'

where 1=1
and BASE1.AssetID = 'abcd'

UNION ALL

SELECT distinct
BASE1.AssetID AS a
,BASE1.Name As b
,case
when AST.form_name01 = 'HPD:Help Desk' then 'Incident'
when AST.form_name01 = 'CHG:Infrastructure Change' then 'Infrastructure Change'
when AST.form_name01 = 'PBM:Known Error' then 'Known Error'
when AST.form_name01 = 'PBM:Problem Investigation' then 'Problem Investigation'
when AST.form_name01 = 'RMS:Release' then 'Release'
when AST.form_name01 = 'PBM:Solution Database' then 'Solution Database' end As c
,replace(CMDB_Associations.Alias_Value COLLATE DATABASE_DEFAULT,char(10),'') AS d
,AST.Request_id01 AS e
,AST.Request_Description01 AS f
,BASE1.Company AS g

FROM
BMC_CORE_BMC_BaseElement BASE1 (NOLOCK)
LEFT JOIN BMC_CORE_BMC_BaseRelationship RELA (NOLOCK)
ON BASE1.InstanceId = RELA.Source_InstanceId
LEFT JOIN BMC_CORE_BMC_BaseElement BASE2 (NOLOCK)
ON RELA.Destination_InstanceId = BASE2.InstanceId AND Base2.DatasetId='BMC.ASSET'
LEFT JOIN AST_CMDB_Associations AST (NOLOCK)
ON AST.request_id02 = BASE1.ReconciliationIdentity
and AST.Request_ID02 != '0' and AST.Request_ID01 != '0'
and AST.form_name01 <> 'TMS:TASK' and AST.form_name01 <> 'WOI:WorkOrder'

where 1=1
and BASE1.AssetID = 'abcd'
) x
group by A, b, g

These queries are identical after the FROM. Any chance you can consolidate this so you only need 1 query?

As a side note, why all the nolock hints?


--EDIT--
Didn't refresh before posting. It seems Chris has much the same comment as me.


A second pair of eyes and the same conclusion - that's good enough for me, Sean.


“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 #1369036
Posted Friday, October 5, 2012 8:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:45 AM
Points: 20, Visits: 127
Sean Lange (10/5/2012)
Still nothing to test with but I think this might be close to what you want...

select A, b, MAX(C), MAX(D), MAX(E), MAX(F), g
from
(
SELECT distinct
BASE1.AssetID AS a
,BASE1.Name As b
,case when BASE2.AssetID like 'AST%' then 'Asset Records' else '' end as c
,replace(RELA.Name,char(10),'') As d
,BASE2.AssetID AS e
,BASE2.Name AS f
,BASE1.Company AS g
FROM
BMC_CORE_BMC_BaseElement BASE1 (NOLOCK)
LEFT JOIN BMC_CORE_BMC_BaseRelationship RELA (NOLOCK)
ON BASE1.InstanceId = RELA.Source_InstanceId
LEFT JOIN BMC_CORE_BMC_BaseElement BASE2 (NOLOCK)
ON RELA.Destination_InstanceId = BASE2.InstanceId AND Base2.DatasetId='BMC.ASSET'
LEFT JOIN AST_CMDB_Associations AST (NOLOCK)
ON AST.request_id02 = BASE1.ReconciliationIdentity
and AST.Request_ID02 != '0' and AST.Request_ID01 != '0'
and AST.form_name01 <> 'TMS:TASK' and AST.form_name01 <> 'WOI:WorkOrder'

where 1=1
and BASE1.AssetID = 'abcd'

UNION ALL

SELECT distinct
BASE1.AssetID AS a
,BASE1.Name As b
,case
when AST.form_name01 = 'HPD:Help Desk' then 'Incident'
when AST.form_name01 = 'CHG:Infrastructure Change' then 'Infrastructure Change'
when AST.form_name01 = 'PBM:Known Error' then 'Known Error'
when AST.form_name01 = 'PBM:Problem Investigation' then 'Problem Investigation'
when AST.form_name01 = 'RMS:Release' then 'Release'
when AST.form_name01 = 'PBM:Solution Database' then 'Solution Database' end As c
,replace(CMDB_Associations.Alias_Value COLLATE DATABASE_DEFAULT,char(10),'') AS d
,AST.Request_id01 AS e
,AST.Request_Description01 AS f
,BASE1.Company AS g

FROM
BMC_CORE_BMC_BaseElement BASE1 (NOLOCK)
LEFT JOIN BMC_CORE_BMC_BaseRelationship RELA (NOLOCK)
ON BASE1.InstanceId = RELA.Source_InstanceId
LEFT JOIN BMC_CORE_BMC_BaseElement BASE2 (NOLOCK)
ON RELA.Destination_InstanceId = BASE2.InstanceId AND Base2.DatasetId='BMC.ASSET'
LEFT JOIN AST_CMDB_Associations AST (NOLOCK)
ON AST.request_id02 = BASE1.ReconciliationIdentity
and AST.Request_ID02 != '0' and AST.Request_ID01 != '0'
and AST.form_name01 <> 'TMS:TASK' and AST.form_name01 <> 'WOI:WorkOrder'

where 1=1
and BASE1.AssetID = 'abcd'
) x
group by A, b, g

These queries are identical after the FROM. Any chance you can consolidate this so you only need 1 query?

As a side note, why all the nolock hints?


--EDIT--
Didn't refresh before posting. It seems Chris has much the same comment as me.


Thanks guys, seems to work fine for one record:

AST000000089076	cdpq-dicrc-arccon-02.SCDPQ	Asset Records	HOSTEDSYSTEMCOMPONENTS	AST000000519575	CDPQ-DICRC-ARCCON-02-ARCS
AST000000089076 cdpq-dicrc-arccon-02.SCDPQ


To

AST000000089076	cdpq-dicrc-arccon-02.SCDPQ	Asset Records	HOSTEDSYSTEMCOMPONENTS	AST000000519575	CDPQ-DICRC-ARCCON-02-ARCS


But when I have multiple records:

AST000000046006	CMOS10D -	Incident	Related to	INC000000042707	INC000000042707: test for GVI
AST000000046006 CMOS10D - Incident Related to INC000000042708 GVI Testing
AST000000046006 CMOS10D - Incident Related to INC000000042709 testggg
AST000000046006 CMOS10D - Incident Related to INC000000048861 INC000000048861: <*** PLEASE DISREGARD *** this is a test Incident Modified
AST000000046006 CMOS10D - Incident Related to INC000000048964 AAAAAAAAAAAAA
AST000000046006 CMOS10D - Known Error Related to PKE000000005002 PKE000000005002: ITSM Issue


I get only the last record:

AST000000046006	CMOS10D -	Known Error	Related to	PKE000000005002	PKE000000005002: ITSM Issue
Post #1369107
Posted Friday, October 5, 2012 8:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:54 PM
Points: 13,471, Visits: 12,329
prady_1988 (10/5/2012)
Sean Lange (10/5/2012)
Still nothing to test with but I think this might be close to what you want...

select A, b, MAX(C), MAX(D), MAX(E), MAX(F), g
from
(
SELECT distinct
BASE1.AssetID AS a
,BASE1.Name As b
,case when BASE2.AssetID like 'AST%' then 'Asset Records' else '' end as c
,replace(RELA.Name,char(10),'') As d
,BASE2.AssetID AS e
,BASE2.Name AS f
,BASE1.Company AS g
FROM
BMC_CORE_BMC_BaseElement BASE1 (NOLOCK)
LEFT JOIN BMC_CORE_BMC_BaseRelationship RELA (NOLOCK)
ON BASE1.InstanceId = RELA.Source_InstanceId
LEFT JOIN BMC_CORE_BMC_BaseElement BASE2 (NOLOCK)
ON RELA.Destination_InstanceId = BASE2.InstanceId AND Base2.DatasetId='BMC.ASSET'
LEFT JOIN AST_CMDB_Associations AST (NOLOCK)
ON AST.request_id02 = BASE1.ReconciliationIdentity
and AST.Request_ID02 != '0' and AST.Request_ID01 != '0'
and AST.form_name01 <> 'TMS:TASK' and AST.form_name01 <> 'WOI:WorkOrder'

where 1=1
and BASE1.AssetID = 'abcd'

UNION ALL

SELECT distinct
BASE1.AssetID AS a
,BASE1.Name As b
,case
when AST.form_name01 = 'HPD:Help Desk' then 'Incident'
when AST.form_name01 = 'CHG:Infrastructure Change' then 'Infrastructure Change'
when AST.form_name01 = 'PBM:Known Error' then 'Known Error'
when AST.form_name01 = 'PBM:Problem Investigation' then 'Problem Investigation'
when AST.form_name01 = 'RMS:Release' then 'Release'
when AST.form_name01 = 'PBM:Solution Database' then 'Solution Database' end As c
,replace(CMDB_Associations.Alias_Value COLLATE DATABASE_DEFAULT,char(10),'') AS d
,AST.Request_id01 AS e
,AST.Request_Description01 AS f
,BASE1.Company AS g

FROM
BMC_CORE_BMC_BaseElement BASE1 (NOLOCK)
LEFT JOIN BMC_CORE_BMC_BaseRelationship RELA (NOLOCK)
ON BASE1.InstanceId = RELA.Source_InstanceId
LEFT JOIN BMC_CORE_BMC_BaseElement BASE2 (NOLOCK)
ON RELA.Destination_InstanceId = BASE2.InstanceId AND Base2.DatasetId='BMC.ASSET'
LEFT JOIN AST_CMDB_Associations AST (NOLOCK)
ON AST.request_id02 = BASE1.ReconciliationIdentity
and AST.Request_ID02 != '0' and AST.Request_ID01 != '0'
and AST.form_name01 <> 'TMS:TASK' and AST.form_name01 <> 'WOI:WorkOrder'

where 1=1
and BASE1.AssetID = 'abcd'
) x
group by A, b, g

These queries are identical after the FROM. Any chance you can consolidate this so you only need 1 query?

As a side note, why all the nolock hints?


--EDIT--
Didn't refresh before posting. It seems Chris has much the same comment as me.


Thanks guys, seems to work fine for one record:

AST000000089076	cdpq-dicrc-arccon-02.SCDPQ	Asset Records	HOSTEDSYSTEMCOMPONENTS	AST000000519575	CDPQ-DICRC-ARCCON-02-ARCS
AST000000089076 cdpq-dicrc-arccon-02.SCDPQ


To

AST000000089076	cdpq-dicrc-arccon-02.SCDPQ	Asset Records	HOSTEDSYSTEMCOMPONENTS	AST000000519575	CDPQ-DICRC-ARCCON-02-ARCS


But when I have multiple records:

AST000000046006	CMOS10D -	Incident	Related to	INC000000042707	INC000000042707: test for GVI
AST000000046006 CMOS10D - Incident Related to INC000000042708 GVI Testing
AST000000046006 CMOS10D - Incident Related to INC000000042709 testggg
AST000000046006 CMOS10D - Incident Related to INC000000048861 INC000000048861: <*** PLEASE DISREGARD *** this is a test Incident Modified
AST000000046006 CMOS10D - Incident Related to INC000000048964 AAAAAAAAAAAAA
AST000000046006 CMOS10D - Known Error Related to PKE000000005002 PKE000000005002: ITSM Issue


I get only the last record:

AST000000046006	CMOS10D -	Known Error	Related to	PKE000000005002	PKE000000005002: ITSM Issue


Are you familiar with aggregate data? Notice the query has MAX and group by. That means it is only going to get 1 row. If you need assistance with this you need to post ddl and sample data. We are totally shooting blind. Take a look at the first link in my signature for best practices when posting questions.

I have to ask again....why the NOLOCK hints?


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1369116
Posted Monday, October 8, 2012 6:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:45 AM
Points: 20, Visits: 127
I apologize for posting the wrong way, I was in a hurry and did not go through the best practices. Anyway, the issue is solved now, used a couple of sub queries, case statements and business logic, not even using the UNION now.

And I removed the NOLOCK hints (thanks for pointing it out ), I was just modifying an existing stored procedure.


Thanks a lot for your time and help,
Prady
Post #1369752
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse