SQL table Widget - Pulling in CI data on Work Item

AJ_Wittenbrink

So i am having an issue, where I am attempting to add CI data on a list of PR records.  All fo the columns appear except for the Asset name that i pulled across.

I found the old Vimeo video where they talk about pulling in a bug (and Oddly during that section when the presenter attempted it, it did not work multiple times (38 minute mark).

I can create a simple query that shows properly in the SQL table widget, as shown below:

SELECT TOP 25
   wi.WorkItemId AS 'Id',
    wi.Title,
	CItem.DisplayName

FROM
   WorkItem AS wi

   JOIN
      Affected_CI$WorkItem_ConfigurationItem AffCI
      ON wi.Id = AffCI.WorkItemId
   JOIN
      ConfigurationItem CItem 
      ON AffCI.ConfigurationItemId = CItem.Id 

However, when I expand that that to match on a problem record, all of the fields show, except for the configuration item.
Code

SELECT
   wi.WorkItemId AS 'Id',
   wi.Title,
   dsstatus.DisplayString AS 'Status',
   dscategory.DisplayString AS 'Category',
   dssource.DisplayString AS 'Source',
   CASE
      WHEN
         wi.AssignedUser IS NULL 
      THEN
         'UnAssigned' 
      ELSE
         wi.AssignedUser 
		END AS AssignedTo,

   CONVERT(VARCHAR, DATEADD(hh, - 5, wi.Created), 100) 'Created', 
   CONVERT(VARCHAR, DATEADD(hh, - 5, wi.LastModified), 100) 'LastModified'

   ,CItem.DisplayName


FROM
   WorkItem AS wi
   LEFT JOIN
      DisplayString dsstatus 
      ON dsstatus.ElementID = wi.StatusId
	  AND dsstatus.LocaleID = 'ENU'
   LEFT JOIN
      DisplayString dscategory 
      ON dscategory.ElementID = wi.CategoryId
	  AND dscategory.LocaleID = 'ENU'
   LEFT JOIN
      DisplayString dssource 
      ON dssource.ElementID = wi.SourceId
	  AND dssource.LocaleID = 'ENU'

	LEFT JOIN
		Affected_CI$WorkItem_ConfigurationItem AffCI
		ON AffCI.WorkItemId = wi.Id<br>	LEFT JOIN
		ConfigurationItem CItem 
		ON AffCI.ConfigurationItemId = CItem.Id <br>
WHERE
   wi.ClassId = '422afc88-5eff-f4c5-f8f6-e01038cde67f' 
  AND wi.StatusId NOT IN
   (
      '25EAC210-E091-8AE8-A713-FEA2472F32FF', '7FF92B06-1694-41E5-2DF7-B4D5970D2D2B'
   )

What I see in SQL


What I see in Cireson



Any ideas on what I am missing here?

Brian_Wiest

I have run into this issue that is the first row contains null it does not display. I have had to add case statements to some columns to account for null. 
Here is the problem ticket that tracked it https://support.cireson.com/Problem/Edit/PR63531/
It is reported to be fixed (and it did for my issue) in the latest release. 

Brian_Wiest

I have run into this issue that is the first row contains null it does not display. I have had to add case statements to some columns to account for null. 
Here is the problem ticket that tracked it https://support.cireson.com/Problem/Edit/PR63531/
It is reported to be fixed (and it did for my issue) in the latest release. 

AJ_Wittenbrink

Worked like a charm!  Thanks.