GROUP BY and text field

[lb1]

In the following query the 'T.Cprofile' datatype is ntext and therefore I get an error telling me that I cannot execute a group by close on this datatype.

SELECT t.ProfType, T.AdvType, T.Cprofile FROM P_SEARCH RIGHT JOIN T ON P_SEARCH.REG_ID = T.REG_ID
GROUP BY T.ProfType, T.AdvType, T.Cprofile
HAVING (((P_SEARCH.Cat)= @ValDat))
ORDER BY T.ProfType;


Is there a way to group the other values and retrieve the value of T.Cprofile for each of the grouped value?

Is there a way to create a sub query or other that will give me a result?

Thanks for any information.
louis

 

[r937]

i'm not really sure i understand your query -- right joins always throw me off, i just cannot picture them in my mind, and i always want to rewrite them as left joins...

however, i think the HAVING is wrong

try

SELECT t.ProfType, T.AdvType, T.Cprofile
FROM P_SEARCH
RIGHT JOIN T
ON P_SEARCH.REG_ID = T.REG_ID
WHERE P_SEARCH.Cat = @ValDat
GROUP BY T.ProfType, T.AdvType, T.Cprofile
ORDER BY T.ProfType;
rudy

http://rudy.ca/

 

[Jocsta]

You can not use text, ntext, or image in a group by expression. Do you really need a ntext column, or could you use a nvarchar?

<< JOC >>

 

[Sqlsa]

Hi,
There are two issues here.
(1) You can't use a ntext,text or image datatyped columns in a group by clause.
(2) If the datatype can be changed to nvarchar without affecting your application, then it is the best solution.
(3) But if the column has to be of ntext datatype, then you can try this.
Create on more column in the table which would be like an abbreviation for the data in ntext coulmn. So, this abbreviation represents the data in ntext column. Now you can use this abbreviation column in your group clause or distinct clause.
But you have to create a mechanism for creating the abbreviation while you input the data to database. Also if there is lot of data in the table, then you should also create a mechanism for upadting the abbreviation column.

Suppose the data in table is as follows:

Col1 Col2(ntext)
------ -----------
1 A..............
2 B..............
3 XYZ
4 XYZ
5 C......

Add a column of simple datatype probably varchar and update the data in Col3.

Col1 Col2(ntext) Col3(Col2 Abbreviation)
------ -----------
1 A............... ABBREVIATION_1
2 B............... ABBREVIATION_2
3 XYZ ABBREVIATION_3
4 XYZ ABBREVIATION_3
5 C...... ABBREVIATION_4

Now you can use Col3 in place of Col2 for using it in &quot;Group By&quot; clause or DISTINCT clause.

Hope this works.

Thanks,

Sreenivas
---------

 

[Sqlsa]

Hi,
There are two issues here.
(1) You can't use a ntext,text or image datatyped columns in a group by clause.
(2) If the datatype can be changed to nvarchar without affecting your application, then it is the best solution.
(3) But if the column has to be of ntext datatype, then you can try this.
Create on more column in the table which would be like an abbreviation for the data in ntext coulmn. So, this abbreviation represents the data in ntext column. Now you can use this abbreviation column in your group clause or distinct clause.
But you have to create a mechanism for creating the abbreviation while you input the data to database. Also if there is lot of data in the table, then you should also create a mechanism for upadting the abbreviation column.

Now you can use ABBREVIATION column in place of ntext coulmn for using it in &quot;Group By&quot; clause or DISTINCT clause.

Hope this works.

Thanks,

Sreenivas
---------

 

[lb1]

Clever way to do it.
I just have to find a way to pickup the first 30 characters of each ntext values and copy to the abbreviation column.
I will try to make a trigger to do this.

Thanks