Can you cast ntext into text so you can use group by?

[James]

I'm trying to create a custom report from MS ProjectServer 2003 to group
actuals by information in a MS Project custom text field.

Is there a way of casting ntext data from my select statement so that I can
use Group by?

Thanks,

James

 

[Stephen Sanderlin]

Yes. CAST(nTextField AS NVARCHAR).

--
Stephen Sanderlin
Principal Consultant
MSProjectExperts

For Project Server Consulting: http://www.msprojectexperts.com
For Project Server Training: http://www.projectservertraining.com

Read my blog at: http://www.projectserverhelp.com
Join the community at: http://forums.epmfaq.com

 

[James]

Thanks for your reply Stephen.

I tried putting the cast statement in the select statement, the Group By
statement and both but still get the following error:

The text, ntext, and image data types cannot be compared or sorted, except
when using IS NULL or LIKE operator.

Here is the query I'm trying to use group by with:

DECLARE
@StartDt As DateTime,
@EndDt As DateTime

SET @StartDt = '03/13/2007'
SET @EndDt = '09/26/2008'

SELECT


CAST(dbo.MSP_VIEW_PROJ_TASKS_CF.TaskText5 AS NVARCHAR),
(ROUND(dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeActualWork / 60000,1))
AS ACTUAL_WRK

FROM dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY

INNER JOIN
dbo.MSP_ASSIGNMENTS ON
dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.ProjectUniqueID =
dbo.MSP_ASSIGNMENTS.PROJ_ID AND
dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentUniqueID =
dbo.MSP_ASSIGNMENTS.ASSN_UID

INNER JOIN
dbo.MSP_TASKS ON
dbo.MSP_ASSIGNMENTS.PROJ_ID = dbo.MSP_TASKS.PROJ_ID AND
dbo.MSP_ASSIGNMENTS.TASK_UID = dbo.MSP_TASKS.TASK_UID

INNER JOIN
dbo.MSP_VIEW_PROJ_TASKS_CF ON
dbo.MSP_VIEW_PROJ_TASKS_CF.CF_TaskUniqueID = dbo.MSP_TASKS.TASK_UID AND
dbo.MSP_VIEW_PROJ_TASKS_CF.WPROJ_ID = 165 AND
dbo.MSP_TASKS.PROJ_ID = 62

INNER JOIN
dbo.MSP_VIEW_PROJ_TASKS_STD ON
dbo.MSP_VIEW_PROJ_TASKS_CF.CF_TaskUniqueID =
dbo.MSP_VIEW_PROJ_TASKS_STD.TaskUniqueID AND
dbo.MSP_VIEW_PROJ_TASKS_CF.WPROJ_ID = dbo.MSP_VIEW_PROJ_TASKS_STD.WPROJ_ID

WHERE
dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeStart >= @StartDt
AND dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeStart <= @EndDt
AND( dbo.MSP_VIEW_PROJ_TASKS_CF.TaskText4 Like 'test')
AND( dbo.MSP_VIEW_PROJ_TASKS_CF.TaskText5 IS NOT NULL)
AND( dbo.MSP_VIEW_PROJ_TASKS_STD.TaskName IS NOT NULL)
AND dbo.MSP_VIEW_PROJ_TASKS_CF.WPROJ_ID = 165

GROUP BY dbo.MSP_VIEW_PROJ_TASKS_CF.TaskText5

 

[James]

Second line of Select statment should read:
SUM(ROUND dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeActualWork /
60000,1))
AS ACTUAL_WRK