Hi.
Finally, i've modified the join on resourcesAssociation. Put normal join instead of left join was not enough.
A test on the ChildResourceGuid was necessary to make correct join. Here is the final query:inner join
select distinct vi.[Name] As [AssetName],
va.[_ResourceGuid] [_ResourceGuid], va.[Asset Type] [Asset Type], va.[Asset Tag] [Asset Tag], htLoc.Name [Location], va.[Serial Number] [Serial Number], va.[System Number]
from
vAsset va
left outer Join vItem vi on vi.[Guid] = va.[_ResourceGuid]
join ResourceTypeHierarchy rth
on (rth.ResourceTypeGuid = va._ResourceTypeGuid)
left join ResourceAssociation raAL
on (raAL.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C')
and (raAL.ParentResourceGuid = va._ResourceGuid)
inner join fnAssetHierarchyTreeExcludeChildren
(
'834BC951-D70F-48F4-9E8E-D7E32C68788D',
'',
default,
'%LocationId%',
default
) htLoc
on (htLoc.Guid=raAL.ChildResourceGuid)
order by [Asset Type], htLoc.[Name]
All seems to be good now.
thanks for your help.