Trying to Cast Ntext as nvarchar in order to use Group BY

J

James

In trying to use Group By for an nText field, I tried putting the cast
statement in the select statement, the Group By statement and both 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),
--************************************************
SUM(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

Thanks,

James
 
R

Rod Gill

Hi,

Try something like:

GROUP BY CAST(dbo.MSP_VIEW_PROJ_TASKS_CF.TaskText5 AS nvarchar(50))

I don't have a 2003 image to play with at the moment so this is from memory.
Good luck!


--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com
 
J

James

Hi Rod,

Thanks for your response.

I no longer get the original error but am now getting a "Column
'dbo.MSP_VIEW_PROJ_TASKS_CF.TaskText5' is invalid in the select list because
it is not contained in either an aggregate function or the GROUP BY clause."
error.

Thanks,

James

If I substitute
 
J

James

Hi Rod,

I think I solved the problem by using a table variable. Not sure if this is
more complicated a solution than it should be:

Declare @TestHoursByFeature TABLE(

TaskText5 nvarchar(50),
AssignmentTimeActualWork decimal)

DECLARE
@StartDt As DateTime,
@EndDt As DateTime

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

INSERT INTO @TestHoursByFeature (TaskText5,AssignmentTimeActualWork)

SELECT


CAST(dbo.MSP_VIEW_PROJ_TASKS_CF.TaskText5 AS nvarchar(50)),
(ROUND(dbo.MSP_VIEW_PROJ_ASSN_TP_BY_DAY.AssignmentTimeActualWork / 60000,1))

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


Select
TaskText5,
SUM(AssignmentTimeActualWork)AS ACTUAL_WRK
From
@TestHoursByFeature
GROUP BY
TaskText5
 
R

Rod Gill

Glad you solved it as my SQL Server skills are fairly basic. I was getting
close to "Ask in a SQL Server group" stage!

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com
 
J

James

No problem!

I also found that I needed to add the line:

SET NOCOUNT ON

at the top of the query to compensate for the fact that using a table
variable results in a closed recordset.

James
 
Top