Home› Analyst Portal

SQL Query to show System.WorkItemAssignedToUser OR not set in result

Jan_SchulzJan_Schulz Customer Adept IT Monkey ✭✭
in Analyst Portal
Hello,
I'm just desperate to output a query for active porn problems with different columns. The results should also show the value assigned to the user when the user is set, otherwise an empty value. But I only get the results of problems that have a user assigned to them. How can I query it correctly? Here my query:
<div>SELECT </div><div>&nbsp;&nbsp;&nbsp;&nbsp;Problem.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C AS [Id],</div><div>&nbsp;&nbsp;&nbsp;&nbsp;Problem.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 AS Titel,</div><div>&nbsp;&nbsp;&nbsp;&nbsp;ProblemStageDisplayStringView.DisplayName AS Phase,</div><div>&nbsp;&nbsp;&nbsp;&nbsp;AssignedUser.DisplayName AS "Zugewiesen an",</div><div>&nbsp;&nbsp;&nbsp;&nbsp;Entity.LastModified AS "Zuletzt ge�ndert",</div><div>&nbsp;&nbsp;&nbsp;&nbsp;Problem.KnownError_4CE16CF0_5098_3CBD_3F72_72C2C5201FDB AS "KnownError",</div><div>&nbsp;&nbsp;&nbsp;&nbsp;ProblemSourceDisplayStringView.DisplayName AS "Quelle",</div><div>&nbsp;&nbsp;&nbsp;&nbsp;Problem.Priority_B930B964_A1C4_0B5A_B2D1_BFBE9ECDC794 AS "Priorit�t",</div><div>&nbsp;&nbsp;&nbsp;&nbsp;ProblemClassificationDisplayStringView.DisplayName AS "Klassifizierung"</div><br><div>FROM BaseManagedEntity AS Entity</div><br><div>--Problem</div><div>INNER JOIN dbo.MTV_System$WorkItem$Problem AS Problem ON Problem.BaseManagedEntityId = Entity.BaseManagedEntityId</div><br><div>--Phase</div><div>LEFT JOIN MT_ClassExtension_326d379e_299a_4014_8655_06e7831df05c AS ProblemStageExt ON ProblemStageExt.BaseManagedEntityId = Problem.BaseManagedEntityId</div><div>LEFT JOIN DisplayStringView AS ProblemStageDisplayStringView ON ProblemStageDisplayStringView.LTStringId = ProblemStageExt.Stage_564BBEA0_22D0_25DE_7126_F4FF26E91E5F AND ProblemStageDisplayStringView.LanguageCode = 'DEU'</div><br><div>--Source</div><div>LEFT JOIN DisplayStringView AS ProblemSourceDisplayStringView ON ProblemSourceDisplayStringView.LTStringId = Problem.Source_E310C86B_A6E0_816F_4E9D_F92D6E75EC7A AND ProblemSourceDisplayStringView.LanguageCode = 'DEU'</div><br><div>--Priority</div><div>LEFT JOIN DisplayStringView AS ProblemClassificationDisplayStringView ON ProblemClassificationDisplayStringView.LTStringId = Problem.Classification_C7A64916_0253_4710_4899_441928F6F2FA AND ProblemClassificationDisplayStringView.LanguageCode = 'DEU'</div><br><div>--AssignedUser</div><div>RIGHT JOIN Relationship AS Rel ON Rel.SourceEntityId = Entity.BaseManagedEntityId</div><div>INNER JOIN RelationshipType AS RelType ON RelType.RelationshipTypeId = Rel.RelationshipTypeId</div><div>&nbsp;&nbsp;&nbsp;&nbsp;AND RelType.RelationshipTypeName = 'System.WorkItemAssignedToUser'</div><div>&nbsp;&nbsp;&nbsp;&nbsp;AND Rel.IsDeleted = '0'</div><div>LEFT JOIN BaseManagedEntity AS AssignedUser ON AssignedUser.BaseManagedEntityId = Rel.TargetEntityId</div><br><div>WHERE</div><div>&nbsp;&nbsp;&nbsp;&nbsp;Problem.Status_3C8876F4_FCBF_148C_FBAF_4CF4F02C6187 = <span>'720438eb-ba08-1263-0944-6791fcb48991'</span></div>


I hope there is a SQL query expert out  there :-)
It really makes me mad.
thanks in advance

Best Answer

Answers

  • Jan_SchulzJan_Schulz Customer Adept IT Monkey ✭✭
    Thank you. Is this query against the Service Manager Database or against the Cireson Database? Because in the Service Manager Database i cant check against a NULL Value (assignedToUser). I solved my problem with a subselect. Not brilliant but it worked i think :-) But your answer is the right thing if we didn´t updated our Problem Workitem with additional properties and query the Cireson Database. so i set the post as the answer. Thanks
  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    The script runs against the Cireson DB, then you will see a left join for the SCSM database. Just need to update that to your Instance and DB name. 
    [ServiceManager].[dbo].[MTV_System$WorkItem$Problem]
    The script I shared does see the null for assigned to user and if null, it updates the displayed value to Unassigned for easy reading on the grid. 
    The left join is due to Cireson doesn't cache the Known issue Boolean 

Sign In or Register to comment.