display function in sql

hi dishanf,

1. when i parse the code all is ok, when i run i get this msg

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.


2. when i parse the code all is ok, when i run i get this msg
Server: Msg 279, Level 16, State 2, Line 1
The text, ntext, and image data types are invalid in this subquery or aggregate expression.
Server: Msg 279, Level 16, State 1, Line 1
The text, ntext, and image data types are invalid in this subquery or aggregate expression.


any ideas?

use
CAST(projnotes AS nvarchar)


select locationprojid, CAST(projnotes AS nvarchar), CAST(objectnotes AS nvarchar) from msm_svccalltable
where projnotes like '%warr%'
GROUP BY locationprojid,CAST(projnotes AS nvarchar), CAST(objectnotes AS nvarchar)

Dont use text column normally
use varchar or nvarchar data types

select locationprojid, CAST(projnotes AS nvarchar), CAST(objectnotes AS nvarchar) from msm_svccalltable
where projnotes like '%warr%'
GROUP BY locationprojid,CAST(projnotes AS nvarchar), CAST(objectnotes AS nvarchar)


this works! but i have no column names for field projnotes and objectnotes, can you code into the script the field names please??

thanks!

also the field projnotes needs to display a text size of 200 chars can this be coded in also>?

thanks!

select locationprojid, CAST(projnotes AS nvarchar) AS 'ColName' , CAST(objectnotes AS nvarchar) from msm_svccalltable
where projnotes like '%warr%'
GROUP BY locationprojid,CAST(projnotes AS nvarchar), CAST(objectnotes AS nvarchar)

SUBSTRING(CAST(projnotes AS nvarchar),1,200) AS 'ColName'

OR Use LEFT or RIGHT Functions

SELECT LEFT('abcdef',2)
>>
'ab'

thanks,

can you put it all together for me please?
?

select
      locationprojid,
      SUBSTRING(CAST(projnotes AS nvarchar),1,200) AS 'projnotes',
      CAST(objectnotes AS nvarchar) AS 'objectnotes'
from msm_svccalltable
where projnotes like '%warr%'
GROUP BY locationprojid,SUBSTRING(CAST(projnotes AS nvarchar),1,200), CAST(objectnotes AS nvarchar)

locatprojid  projectnotes
0767BP      This site is an Independent Si      NULL
1142      All motor spirit pumps have be      NULL
1148      Marconi site ref # 3051003  As      NULL
1153      All motor spirit pumps have be      NULL
1169      All motor spirit pumps have be      NULL
1169      All motor spirit pumps have be      Covered under PEC Warranty. 12


its works well. but the project notes field is too short still ??? this field has about 50-200 chars in it, but it is getting truncated

what happnes if you remove the substring and just leave it as it is? is it trucated yet?
post the result as above

NOT SURE I UNDERSTAND WHY
you've got the group by in there in the first place...

are you sure you just don't want

select
     locationprojid,
     projnotes,objectnotes from msm_svccalltable
where projnotes like '%warr%'
order by 1

select locationprojid, CAST(projnotes AS nvarchar) AS 'ColName' , CAST(objectnotes AS nvarchar) from msm_svccalltable
where projnotes like '%warr%'
GROUP BY locationprojid,CAST(projnotes AS nvarchar), CAST(objectnotes AS nvarchar)

i used the older query. [above] without subctring and it is still truncated.

1148      Marconi site ref # 3051003  As      NULL
1153      All motor spirit pumps have be      NULL
1169      All motor spirit pumps have be      NULL

lowfatspread, your script works but i want the group by function to group the identical records

BTW,
SUBSTRING(CAST(projnotes AS nvarchar),1,200) AS 'projnotes',
could write
CAST(projnotes AS nvarchar(200)) AS 'projnotes',

>>the project notes field is too short still ??? <<
If you run the query in Query Analyser, chech menu Tools/Options , results tab, value for "maximum characters per column"  , most likely it's just a display issue

thanks for the help hilaire, the maximium length of the chars is 256, i set to 999. still the same

change
CAST(projnotes AS nvarchar) AS 'projnotes',
to
CAST(projnotes AS nvarchar(200)) AS 'projnotes',

both are the same... isnt it??

No
Read BOL, last line
Cast defaults to nvarchar(30)

<Copied from BOL (Books online)>
nchar and nvarchar
Character data types that are either fixed-length (nchar) or variable-length (nvarchar) Unicode data and use the UNICODE UCS-2 character set.

nchar(n)

Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size is two times n bytes. The SQL-92 synonyms for nchar are national char and national character.

nvarchar(n)

Variable-length Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered. The data entered can be 0 characters in length. The SQL-92 synonyms for nvarchar are national char varying and national character varying.

Remarks
When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.
</BOL>

select
     locationprojid,
     SUBSTRING(CAST(projnotes AS nvarchar),1,200) AS 'projnotes',
     CAST(objectnotes AS nvarchar) AS 'objectnotes'
from msm_svccalltable
where projnotes like '%warr%'
GROUP BY locationprojid,SUBSTRING(CAST(projnotes AS nvarchar),1,200), CAST(projnotes AS nvarchar(200)) AS 'projnotes',


which line am i changing???

Oh.. Thanks Hilaire

spot on! thank you

i will split points between you and dishanf??