SET
TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
USE
FIMService
GO
SELECT DOwn.*
FROM
(
SELECT
groupObjID = G.[objectID]
, GroupDisplayName = GAOVS.ValueString
, userDisplayName=
UAOVS.ValueString
, UserObjID = U.[objectid]
FROM
[fim].[ObjectValueReference] GOVR
JOIN
[fim].[ObjectValueString] GAOVS
ON
GOVR.ObjectKey = GAOVS.ObjectKey
JOIN
[fim].[Objects] G
ON
G.ObjectKey = GOVR.ObjectKey
JOIN
[fim].[ObjectValueString] UAOVS
ON
GOVR.ValueReference = UAOVS.ObjectKey
JOIN
[fim].[Objects] U
ON
U.ObjectKey = GOVR.ValueReference
WHERE
GOVR.[AttributeKey] =65 -- DisplayedOwner
AND
UAOVS.[AttributeKey] = 1
AND GAOVS.[AttributeKey] = 1 -- DisplayedName
)
DOwn --DisplayedOwners
LEFT
JOIN
(
SELECT groupObjID = G.[objectID]
, GroupDisplayName = GAOVS.ValueString
, userDisplayName =
UAOVS.ValueString
, UserObjID = U.[objectid]
FROM
[fim].[ObjectValueReference] GOVR
JOIN
[fim].[ObjectValueString] GAOVS
ON
GOVR.ObjectKey = GAOVS.ObjectKey
JOIN
[fim].[Objects] G
ON
G.ObjectKey = GOVR.ObjectKey
JOIN
[fim].[ObjectValueString] UAOVS
ON
GOVR.ValueReference = UAOVS.ObjectKey
JOIN
[fim].[Objects] U
ON
U.ObjectKey = GOVR.ValueReference
WHERE
GOVR.[AttributeKey] =138 -- Owner
AND
UAOVS.[AttributeKey] = 1
AND GAOVS.[AttributeKey] = 1 --DisplayedName
)
Own -- Owners
On
Down.gObjID = Own.gObjID
AND Down.UObjID = Own.UObjID
WHERE
Own.UObjID IS NULL
order
by DOwn.GacctName