Asset Management Suite

I am writing a query of my 6.5 database that I will use in a connector to import Asset data into my 7.1 environment.   I have a question about a query to pull in a few additional pieces of data for me to complete my migration from 6.5 to 7.1.

I have written a query for my 6.5 environment (at the bottom of this message) that appears to work correctly.  However, I few issues:

1. The [Purchase Order] field from the Inv_Cost_Items is a guid.  I would like it to be a PO number like it appears in the Inv_Accounting_Information table (example of a PO number in our environment: P1203456_4).

2. The [Owner] field form the Inv_Ownership_Details table is a guid.  I would like it to be  reflective of the actual name of the owner. (I am able to join on vitem for the name where Inv_Ownership_Details.owner = vi.guid, which does returns the owner name but I am unable to merge those results into my query?).

-- Begin Query

select

va.[asset type],

va.status,

va.manufacturer,

va.model,

va.[serial number],

va.barcode,

vl.name AS 'Location',

ci.[Purchase Order],

ci.amount AS 'Cost',

iai.[Available for Use],

iai.[Planned Disposal Date],

iod.owner,

com.comment

--ai.type

--as.owner from 6.5, which maps to cost center in 7.1

--as.cost center nope, cause that is always 0201 in 6.5

from vasset va

-- Location

left join ResourceAssociation ra

                on ra.ParentResourceGuid = va._ResourceGuid      

                and ra.ResourceAssociationTypeGuid ='05DE450F-39EA-4AAE-8C5F-77817889C27C'               

left join vLocation vl on ra.ChildResourceGuid = vl._ResourceGuid

-- Accounting Info

left join Inv_Accounting_Information iai

on va._resourceguid = iai._resourceguid

-- PO

left join Inv_Cost_Items ci

                on ci._resourceguid = va._resourceguid

-- Cost Center

left join Inv_Ownership_Details iod

                on va._resourceguid = iod._resourceguid

-- Comment

left join Inv_Comment com

                on com._resourceguid = va._resourceguid

where va.status <> 'disposed'

-- End Query

OK, so to clarify, this query gives me the PO number in the format I am looking for:

select vi.name from Inv_Cost_Items ic
join vitem vi
on ic.[purchase order] = vi.guid

Now how do I integrate that into my query in my post?

Exactly what I've been trying to figure out.  If you post this to StackOverflow it'll be answered in under five minutes.  That's what I usually do when I get stuck with SQL.  I imagine you need to create a temporary table that you can then join.  I never do stuff like that so I haven't learned how yet.

Temp tables is the way I have gotten stuff like this.

--Begin Query

SELECT va.[Asset Type], va.Status, va.Manufacturer, va.Model, va.[Serial Number], va.Barcode, vl.Name AS Location, iai.[Available for Use],

iai.[Planned Disposal Date], com.Comment, od.Owner, po.[Purchase Order], po.Amount

FROM (SELECT iod._ResourceGuid, vio.Name AS Owner

FROM dbo.vItem AS vio RIGHT OUTER JOIN

dbo.Inv_Ownership_Details AS iod ON vio.Guid = iod.Owner) AS od RIGHT OUTER JOIN

(SELECT ici._ResourceGuid, vic.Name AS [Purchase Order], ici.Amount

FROM dbo.vItem AS vic RIGHT OUTER JOIN

dbo.Inv_Cost_Items AS ici ON vic.Guid = ici.[Purchase Order]) AS po RIGHT OUTER JOIN

dbo.vAsset AS va ON po._ResourceGuid = va._ResourceGuid ON od._ResourceGuid = va._ResourceGuid LEFT OUTER JOIN

dbo.ResourceAssociation AS ra ON va._ResourceGuid = ra.ParentResourceGuid AND

ra.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C' LEFT OUTER JOIN

dbo.vLocation AS vl ON ra.ChildResourceGuid = vl._ResourceGuid LEFT OUTER JOIN

dbo.Inv_Accounting_Information AS iai ON va._ResourceGuid = iai._ResourceGuid LEFT OUTER JOIN

dbo.Inv_Comment AS com ON va._ResourceGuid = com._ResourceGuid

WHERE (va.Status <> 'disposed')

 --end query