text, ntext, or image data type cannot be selected as Distinct????

[kaul125]

I'm using MS SQL Server 7 with PB 6.5 and I'm getting the following error when retrieving my datawindow:

Select error: The text, ntext or image data type cannot be selected as DISTINCT.

Ond of the fields in the dw is of 'text' datatype.

My datawindow has multiple select statements using 'UNION' and none of the select statements is using the key word 'DISTINCT'. The datawindow is just a report, meaning its being used as a datawindow just to display results to the user. The user cannot update this datawindow.

Why am I getting this error and how do I fix it?

Keith

 

[BetterLateThanNever]

Hi,

Are any of the columns being retrieved of type text, ntext or image?
Do you get a different result if you specify

UNION ALL

?

Cheers.

 

[BetterLateThanNever]

Hi,

Ignore the previous post, I've just read your post correctly now. A basic

UNION

will perform a

DISTINCT

anyway, because by default the

UNION

operator returns the unique rows unless you specifiy

UNION ALL

. So the SQL is trying to do a unique operation on the text column and failing. You could get round this by casting (or

CONVERT

ing) the text column to a varchar or the like. Do you really need to return the text column at this stage? Will it be populated with an indeterminate length of text? PB allows you to define textual columns with a high character count (32000 characters or so I believe).

Cheers.

 

[kaul125]

Thanks. I did a cast() function to convert it to a varchar and it eliminated this error. However, I get another error:

"select error: Data-conversion resulted in overflow"

Do you have any idea what this means and how to correct this?

Since this datawindow is used as a report for the user, I do need to return this "text" column for the user to see. When I export the datawindow, the column is specified as:

column=(type=char(32766)......

What do you mean by your question "Will it be populated with an indeterminate length of text?" Can you explain further?

Thanks,

keith

 

[BetterLateThanNever]

Hi,

Try specifying a length in your cast/convert.

What I meant was if you know for a fact that the users will only ever enter 2000 characters (or whatever) you could safely specify a limit on the conversion.

If none of this works you may need to use a composite datawindow, and retrieve the text field separately.

Cheers.