03-03-2023 06:26 PM - last edited on 05-23-2023 09:30 AM by JackLacava
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
Solved! Go to Solution.
03-04-2023 06:31 AM
You're joining on the wrong thing in the PlugAccount subquery. Should be:
LEFT JOIN Member PA ON MP.DecimalValue = PA.MemberId
03-04-2023 06:31 AM
You're joining on the wrong thing in the PlugAccount subquery. Should be:
LEFT JOIN Member PA ON MP.DecimalValue = PA.MemberId
03-06-2023 02:19 AM
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.