-1

I am trying to write a query where two different UIDs need to lookup a Resource Name for both, but separately.

In other words, for each Task, there are resources assigned and one status manager. This converts in SQl to an Assignment, unique to a resource, but with the same status manager. However, no where in the database can one see the Status Manager's Name on a given assignment.

The assignment does have "TaskStatusManagerUID" available. The name of the Status Manager can be determined by tying it back to MSP_EPMResource table where TaskStatusManagerUID = ResourceUID.

The catch is, for my report, I need to be able to look at the ResourceUID and TaskstatusManagerUID and determine the names of each on the same assignment.

While I have been successful with a join to display the name for one or the other, I have not been able to determine how to show the name for both the Resource and TaskStatusManager.

This is an example of what I am trying to display (parentheses added for readability):

(AssignmentUID) (Task Name) (Resource Name) (Task Status Manager Name)

See more info below:

enter image description here

This is the code I have been working with, but have been unsuccessful: 

Select top 100
c.[assignmentuid], 
a.[taskname], 
c.[resourceuid],
b.[resourcename], 
a.[taskstatusmanageruid],
d.[StatusManager]

from [PRJPROD_ProjectWebApp].[dbo].[MSP_EpmAssignment] c
join [PRJPROD_ProjectWebApp].[dbo].[MSP_EpmTask_UserView] a
on a.[TaskUID] = c.[TaskUID]
join [PRJPROD_ProjectWebApp].[dbo].[MSP_EpmResource] b
on b.[ResourceUID] = c.[ResourceUID]

join (select b.resourcename StatusManager
from [PRJPROD_ProjectWebApp].[dbo].[MSP_EpmResource] b) d 
on d.[StatusManager] = a.[taskstatusmanageruid] 

group by 
c.[assignmentuid], 
a.[taskname], 
c.[resourceuid],
b.[resourcename], 
a.[taskstatusmanageruid],
d.[StatusManager]

Currently, I am getting "Conversion failed when converting from a character string to uniqueidentifier."

abauman
  • 96
  • 6

2 Answers2

0

On your joins you have on a.[TaskUID] = c.[TaskUID], on b.[ResourceUID] = c.[ResourceUID], and on d.[StatusManager] = a.[taskstatusmanageruid], of which, I am assuming that the last one is causing you the issue. Try instead

join (select b.resourcename StatusManager
from [PRJPROD_ProjectWebApp].[dbo].[MSP_EpmResource] b) d 
on d.[StatusManager] = CONVERT(CHAR, a.[taskstatusmanageruid])

This will convert the GUID contained in taskstatusmanageruid to a char string, allowing it to compare successfully.

You could also, instead of converting the value, cast the value CAST(a.[taskstatusmanageruid] AS CHAR

EDIT

Due to the nature of the GUID, you may not be able to convert/cast it to a char value, in which case you would need to convert/cast both fields to either varchar or nvarchar:

join (select b.resourcename StatusManager
from [PRJPROD_ProjectWebApp].[dbo].[MSP_EpmResource] b) d 
on CONVERT([N]VARCHAR, d.[StatusManager]) = CONVERT([N]VARCHAR, a.[taskstatusmanageruid])

OR

join (select b.resourcename StatusManager
from [PRJPROD_ProjectWebApp].[dbo].[MSP_EpmResource] b) d 
on CAST(d.[StatusManager] AS [N]VARCHAR) = CAST( a.[taskstatusmanageruid] AS [N]VARCHAR)
Jeff Beese
  • 388
  • 2
  • 5
  • 19
  • Good catch, but it still isn't working. Now it is telling me "Insufficient result space to convert uniqueidentifier value to char." with both the Convert and Cast. Any other ideas? – abauman Mar 26 '15 at 17:19
  • Instead of converting/casting to char, convert/cast both values to varchar or nvarchar, I will edit answer appropriately. – Jeff Beese Mar 26 '15 at 17:20
0

Thanks to Jeff Beese's extra set of eyes, it was enough for me to get the last piece in place!

Select top 100
c.[assignmentuid], 
a.[taskname], 
c.[resourceuid],
b.[resourcename], 
a.[taskstatusmanageruid],
d.[StatusManager]

from [PRJPROD_ProjectWebApp].[dbo].[MSP_EpmAssignment] c
join [PRJPROD_ProjectWebApp].[dbo].[MSP_EpmTask_UserView] a
on a.[TaskUID] = c.[TaskUID]
join [PRJPROD_ProjectWebApp].[dbo].[MSP_EpmResource] b
on b.[ResourceUID] = c.[ResourceUID]

join (select b.resourcename as StatusManager, 
             b.ResourceUID 
from [PRJPROD_ProjectWebApp].[dbo].[MSP_EpmResource] b) d 
on d.[resourceuid] = a.[taskstatusmanageruid]


group by 
c.[assignmentuid], 
a.[taskname], 
c.[resourceuid],
b.[resourcename], 
a.[taskstatusmanageruid],
d.[StatusManager]
abauman
  • 96
  • 6