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. 😛