How to alias the workitem as ID to make the ID clickable when one of the joined tables also has ID
Carol_Morrell
Customer IT Monkey ✭
Help! I have created a dashboard and the SQL works fine until I join in a related item - it works fine in SQL, but in my dashboard it doesn't - I think it's because one of the tables I am joining also has an Id - the SQL that works in the dashboard is:
SELECT Wi.WorkItemId AS [Id], Title, StatusDS.DisplayString AS 'Status', AssignedUser, PriorityId, AffectedUser,
CatDS.DisplayString AS 'Category', TierDS.DisplayString AS 'SupportGroup',
LastModified, Created, isparent
FROM [ServiceManagement].[dbo].[WorkItem] Wi
LEFT JOIN DisplayString [StatusDS] ON StatusId = StatusDS.ElementId AND StatusDS.LocaleID = 'ENU'
LEFT JOIN DisplayString [TierDS] ON TierId = TierDS.ElementId AND TierDS.LocaleID = 'ENU'
LEFT JOIN DisplayString [CatDS] ON CategoryID = CatDS.ElementId AND CatDS.LocaleID = 'ENU'
WHERE ClassID = 'A604B942-4C7B-2FB2-28DC-61DC6F465C68'
AND StatusDS.DisplayString != 'Resolved' AND StatusDS.DisplayString != 'Closed'
But the SQL that I need which works in SQL, but not in the dashboard, has two further joins, see below - but when I add these into the dashboard SQL, it returns no rows (but returns the correct records in SQL) - I am just adding in the Location:
LEFT JOIN Related_CI$WorkItem_ConfigurationItem [RCi] ON wi.Id = RCi.WorkItemId
LEFT JOIN ConfigurationItem [CLoc] ON RCi.ConfigurationItemId = CLoc.Id AND CLoc.ObjectClassName = 'Cireson.AssetManagement.Location'Best Answer
-
OptionsCarol_Morrell
Customer IT Monkey ✭
No worries, I've figured it out, just describing the issue by posting it helped me understand what I was doing wrong - now all sorted!
0
Answers