Hello,
How can I call an account's assigned plug account via SQL? I able to get the PlugAcct DecimalValue, but need help with the syntax to return the account member name instead of the ID...
SELECT
(CASE WHEN D.DimTypeId = 0 THEN 'Entity' WHEN D.DimTypeId = 2 THEN 'Scenario'
WHEN D.DimTypeId = 5 THEN 'Account' WHEN D.DimTypeId = 6 THEN 'Flow'
WHEN D.DimTypeId = 9 THEN 'UD1' WHEN D.DimTypeId = 10 THEN 'UD2'
WHEN D.DimTypeId = 11 THEN 'UD3' WHEN D.DimTypeId = 12 THEN 'UD4'
WHEN D.DimTypeId = 13 THEN 'UD5' WHEN D.DimTypeId = 14 THEN 'UD6'
WHEN D.DimTypeId = 15 THEN 'UD7' WHEN D.DimTypeId = 16 THEN 'UD8'
ELSE 'Unknown'
END)
AS 'DimTypeName',
M.Name
AS 'MemberName',
M.Description
AS 'MemberDesc',
(SELECT TOP 1 MP.TextValue
FROM MemberProperty MP
WHERE MP.MemberId = M.MemberId
AND MP.DimTypeId = 5
AND MP.PropertyId = 700200)
AS 'Formula',
(SELECT Cast(Cast(MP.DecimalValue AS Int) AS Nvarchar)
FROM MemberProperty MP
WHERE MP.MemberId = M.MemberId
AND MP.DimTypeId = 5
AND MP.PropertyId = 700)
AS 'PlugAcctDecimalValue',
(SELECT PA.Name
FROM MemberProperty MP
LEFT JOIN Member PA ON Cast(MP.DecimalValue AS NVarchar) = PA.Name
WHERE MP.MemberId = M.MemberId
AND MP.DimTypeId = 5
AND MP.PropertyId = 700)
AS 'PlugAccount'
FROM
Member M
INNER JOIN Dim D ON M.DimTypeId = D.DimTypeId AND M.DimId = D.DimId
WHERE
M.Name = '29006'
ORDER BY
MemberName
