"Having" and "Like" in a text data type

[scotttom]

Hello

I've need some guidance..

I need a view that is based on a query of a number of fields. One of which "Notes" is a SQL2000 text field. (Unfortunately I can't change it at this point)

SELECT     Project, MIN(Date) AS UPLDate, Comp AS UPLComp
FROM         dbo.Activities
GROUP BY ACode, Comp, Type, Project, Notes, Group
HAVING      (TypeLIKE '%CATALOG%') AND (ACode = 'V') AND (Notes LIKE '%Upload%') AND (Group= 'MKTG')

The issues is of course my old friend ...

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

I AM using a LIKE operator, but I suspect the HAVING is what is preventing this from working.

Thanks in advance for any assistance.

Best,

Scott

 

[bborissov]

SELECT     Project, MIN(Date) AS UPLDate, Comp AS UPLComp
FROM         dbo.Activities
WHERE (Type LIKE '%CATALOG%') AND -- TypeLike is not as Type LIKE :-)
      (ACode = 'V')           AND
      (Notes LIKE '%Upload%') AND
      (Group= 'MKTG')
GROUP BY ACode, Comp, Type, Project, Notes, Group

not tested

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[scotttom]

Thanks for the reply, but I get the same error.

Should be pretty straight forward right?

Confused,

Scott

 

[scotttom]

Got it! I got rid of the unnecessary GroupBy.

Thanks again,

Scott

 

[AlexCuse]

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

(ACode = 'V')

(Group= 'MKTG')

Think you need to change those two to ACode LIKE 'V'...

Hope this helps,

Alex



[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

 

[AlexCuse]

Derr, never mind me ;-)

Can you post the query as you currently have it?

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

 

[scotttom]

Oddly it was the GROUP BY it did not like...

SELECT     Project, MIN(Date) AS UPLDate, Comp AS UPLComp
FROM         dbo.Activities
WHERE     (Type LIKE '%CATALOG%') AND (ACode = 'V') AND (Notes LIKE '%Upload%') AND (Group = 'MKTG')
GROUP BY Project, Comp

Thanks for the replies.

Scott

 

[scotttom]

Sigh.....

So I needed to make a slight change to this query and again it is not working.

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

Notes is a text field.

Can anyone point me in the right direction here?

SELECT  Project, MIN(Date) AS MinUPLDate
      FROM  dbo.Activities
GROUP BY ACode, Type, Project, Notes, group
      HAVING      (Type LIKE '%CATALOG%') AND (Notes LIKE '%Upload%') AND (ACode = 'V') AND (group = 'MKTG')

Thanks in advance for any help!

Scott

 

[scotttom]

If anybody else ever bumps into this...

had to convert...

SELECT     Project, MIN(Date) AS MinUPLDate
    FROM          dbo.Activities   
Where    (Type LIKE '%CATALOG%') AND (Notes LIKE '%Upload%') 
		AND (dbo.mast.ActivityCode = 'V') AND (Responsibility = 'MKTG')
    GROUP BY ACode,  Type, Project, [COLOR=red]Convert(varchar(500),Notes),[/color red] responsibility