error problems

[jamiec1]

the error i keep getting is
Server: Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
Not sure why it is throwing up error but a bit rusty on my sql


code is

SELECT
[JobRecords].[JobID],
[JobRecords].[DateTimeCreated],
[ContractType].[ContractTypeName],
[CustomerContracts].[CompanyName],
[CustomerContracts].[UnitsRemaining],
[CoveredSites].[SiteName],
[JobRecords].[Description],
[JobRecords].[Status],
[ProblemCategory].[ProblemCategory],
[ActionRequired].[Action],
[JobRecords].[PriorityID],
[Priority].[Priority],
IsNull( convert(int , Sum(FurtherJobDetails.UnitsUsed) ) ,0) AS SumOfUnitsUsed

FROM (ProblemCategory INNER JOIN (Priority INNER JOIN ((ContractType INNER JOIN CustomerContracts ON ContractType.ContractTypeID = CustomerContracts.ContractTypeID) INNER JOIN (CoveredSites INNER JOIN (ActionRequired INNER JOIN JobRecords ON ActionRequired.ActionID = JobRecords.ActionID) ON CoveredSites.SiteID = JobRecords.SiteID) ON CustomerContracts.CustomerID = CoveredSites.CustomerID) ON Priority.PriorityID = JobRecords.PriorityID) ON ProblemCategory.ProbCatID = JobRecords.ProbCatID) LEFT JOIN FurtherJobDetails ON JobRecords.JobID = FurtherJobDetails.JobID
WHERE (((CustomerContracts.CustomerID)= 1) AND ((JobRecords.Status) Like 'Open'))

GROUP BY
[JobRecords].[JobID],
[JobRecords].[DateTimeCreated],
[ContractType].[ContractTypeName],
[CustomerContracts].[CompanyName],
[CustomerContracts].[UnitsRemaining],
[CoveredSites].[SiteName],
[JobRecords].[Description],
[JobRecords].[Status],
[ProblemCategory].[ProblemCategory],
[ActionRequired].[Action],
[JobRecords].[PriorityID],
[Priority].[Priority]

ORDER BY JobRecords.JobID

 

[bperry]

Is Description a text field? I'm guessing that SQL doesn't like to Group By that type of column (even though the msg doesn't exactly say that.)

You could perhaps try temporarily dropping the Description from the Select and the Group By, see if that makes a change.

 

[jamiec1]

nice one thats it
problem is i need that field

thanks for your help

 

[bperry]

If it helps, I can say that it's quite unusual to see that many columns in the Group By,, and I would be surprised if there wasn't perhaps a better way to orgainize that statement (and pick up Description to boot). If you needed some help, post a few more comments about what you are doing, and probably someone here might have a suggestion.

bp

 

[jamiec1]

it works with the query below but i will loose info if field over 8000 long

SELECT
[JobRecords].[JobID],
[JobRecords].[DateTimeCreated],
[ContractType].[ContractTypeName],
[CustomerContracts].[CompanyName],
[CustomerContracts].[UnitsRemaining],
[CoveredSites].[SiteName],
CAST([JobRecords].[Description] AS varchar(8000)) as Description,
[JobRecords].[Status],
[ProblemCategory].[ProblemCategory],
[ActionRequired].[Action],
[JobRecords].[PriorityID],
[Priority].[Priority],
IsNull(convert(int,Sum(FurtherJobDetails.UnitsUsed)),0) AS SumOfUnitsUsed

FROM (ProblemCategory INNER JOIN (Priority INNER JOIN ((ContractType INNER JOIN CustomerContracts ON ContractType.ContractTypeID = CustomerContracts.ContractTypeID) INNER JOIN (CoveredSites INNER JOIN (ActionRequired INNER JOIN JobRecords ON ActionRequired.ActionID = JobRecords.ActionID) ON CoveredSites.SiteID = JobRecords.SiteID) ON CustomerContracts.CustomerID = CoveredSites.CustomerID) ON Priority.PriorityID = JobRecords.PriorityID) ON ProblemCategory.ProbCatID = JobRecords.ProbCatID) LEFT JOIN FurtherJobDetails ON JobRecords.JobID = FurtherJobDetails.JobID
WHERE (((CustomerContracts.CustomerID)= 1) AND ((JobRecords.Status) Like 'Open'))

GROUP BY

[JobRecords].[JobID],
[JobRecords].[DateTimeCreated],
[ContractType].[ContractTypeName],
[CustomerContracts].[CompanyName],
[CustomerContracts].[UnitsRemaining],
[CoveredSites].[SiteName],

CAST([JobRecords].[Description] AS varchar(8000)),

[JobRecords].[Status],
[ProblemCategory].[ProblemCategory],
[ActionRequired].[Action],
[JobRecords].[PriorityID],
[Priority].[Priority]

ORDER BY JobRecords.JobID


any other ideas

 

[bperry]

Hi again,

I'm wondering if this produces the same thing. I can't really tell, not being familiar with the relationships, but I'm thinking this will be pretty much the same thing.

If you care to, you can try it and let me know the differences, if any. Hopefully, no syntax errorz.
[tt]
SELECT
jr.JobID,
jr.DateTimeCreated,
ct.ContractTypeName,
cc.CompanyName,
cc.UnitsRemaining,
cs.SiteName,
jr.Description,
jr.Status,
pc.ProblemCategory,
ar.Action,
jr.PriorityID,
p.Priority,
(Select IsNull( convert(int, Sum(UnitsUsed) ) ,0)
From FurtherJobDetails fj
Where fj.JobId = jr.JobIs
) AS SumOfUnitsUsed

FROM JobRecords jr

INNER JOIN Priority p
ON jr.PriorityId = p.PriorityId
INNER JOIN ProblemCategory pc
ON jr.ProbCatId = pc.ProbCatId
INNER JOIN ActionRequired ar
ON jr.ActionId = ar.ActionId
INNER JOIN CoverSites cs
ON jr.SiteId = cs.SiteId
INNER JOIN CustomerContracts cc
ON cs.CustomerId = cc.CustomerId
INNER JOIN ContractType ct
ON cc.ContractTypeId = ct.ContractTypeId

WHERE cc.CustomerID= 1
AND jr.Status = 'Open'

ORDER BY JobRecords.JobID
[/tt]
-----------------------------
I'm wondering if you find it easier to read the way i set up the JOINS. (I do.) Since they are all Inner Joins, the result set should be the same with or without all the nesting.

 

[bperry]

P.S.
I changed the test for Open status to = ,

LIKE is often used with a wildcard:

...When Status LIKE 'Open%'

But when there's no wildcard I think it is the same as equal. If it doesn't seem to be, then change = back to Like.

 

[jamiec1]

thanks bperry

that has solved the problem .. I have been working with access too long and it has made my brain grow weak..

 

[jamiec1]

i used like because i was passing in either open, closed or % from the asp page