JDA help with alias
Can I post JDA questions here?
We're trying to get alias to be listed in a JDA. I've tested the query script in our SQL environment and got on our test case 8 results (as expected).
When I call for it in the JDA, I get 1 result.
Query and JDA scripts below.
WITH CTE AS (
SELECT CAST(1 AS varchar(MAX)) AS RowNum, n.NameID, n.FullName, n.FirstName, n.MiddleName, n.LastName, NULL AS NameID2
FROM jw50Name n
WHERE n.NameID = 23720
UNION ALL
SELECT a.RowNum+','+CAST(rn.NameID1 AS varchar(MAX)), rn.NameID1, n1.FullName, n1.FirstName, n1.MiddleName, n1.LastName, rn.NameID2
FROM devNameToName rn
JOIN jw50Name n1 ON n1.NameID = rn.NameID1
JOIN CTE a ON a.NameID = rn.NameID2
AND a.RowNum NOT LIKE '%,'+CAST(rn.NameID1 AS varchar)+'%'
WHERE rn.Relationship = 'ALIAS'
)
SELECT *
, AliasWithMiddleInitail = ISNULL(CTE.FirstName + ' ', '') + ISNULL(LEFT(CTE.MiddleName,1) + '. ', '') + CTE.LastName
, (SELECT COUNT(FullName) FROM CTE) AS NumOfAlias
FROM CTE
<!-------------------------------------------->
%[If(CustAlias.NumOfAlias>2)] AKA %[ForEach(A in CustAlias)] %[Cust_Alias.AliasWithMiddleInitail] %[If(A.IsLast=False)], %[EndIf] %[EndForEach]%[Else]%[EndIf]
-
Casey Newsom commented
Not an employee. Would this work?
select AliasWithMiddleInitail = (ISNULL(nam.FirstName + ' ', '') + ISNULL(LEFT(nam.MiddleName,1) + '. ', '') + nam.LastName) from devNameToName nn
inner join jw50_name nam on nn.nameid2 = nam.nameid
where Relationship = 'Alias'
and NameID1 = 23720%[If(Cust_Alias.AliasWithMiddleInitail.RecordCount > 0)]AKA %[Cca(Cust_Alias.AliasWithMiddleInitail)]%[EndIf]