Extract Account's Assigned Plug Account via SQL

[sdayringer]

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

[JackLacava · Accepted solution]

You're joining on the wrong thing in the PlugAccount subquery. Should be:

LEFT JOIN Member PA ON MP.DecimalValue = PA.MemberId

[Henning]

Hi - also for possible audience reading this later - please keep in mind to use native functionality in the business rules to, such as in this case, check an account's assigned plug account. Only use SQL when really necessary, which may of course be the case here!

Just adding this as I have seen too many solutions where (complex) SQL was used instead of going the simple way via built-in functionality.