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:

AST000000089076cdpq-dicrc-arccon-02.SCDPQAsset RecordsHOSTEDSYSTEMCOMPONENTSAST000000519575CDPQ-DICRC-ARCCON-02-ARCS

AST000000089076cdpq-dicrc-arccon-02.SCDPQ

To

AST000000089076cdpq-dicrc-arccon-02.SCDPQAsset RecordsHOSTEDSYSTEMCOMPONENTSAST000000519575CDPQ-DICRC-ARCCON-02-ARCS

But when I have multiple records:

AST000000046006CMOS10D -IncidentRelated toINC000000042707INC000000042707: test for GVI

AST000000046006CMOS10D -IncidentRelated toINC000000042708GVI Testing

AST000000046006CMOS10D -IncidentRelated toINC000000042709testggg

AST000000046006CMOS10D -IncidentRelated toINC000000048861INC000000048861: <*** PLEASE DISREGARD *** this is a test Incident Modified

AST000000046006CMOS10D -IncidentRelated toINC000000048964AAAAAAAAAAAAA

AST000000046006CMOS10D -Known ErrorRelated toPKE000000005002PKE000000005002: ITSM Issue

I get only the last record:

AST000000046006CMOS10D -Known ErrorRelated toPKE000000005002PKE000000005002: ITSM Issue