Solved

The text, ntext, and image data types are invalid in this subquery or aggregate expression.

Posted on 2004-08-02
6
523 Views
Last Modified: 2006-11-17
SELECT MIN(Memo) FROM PmtsMemos WHERE PmtsMemos.InfinityRecNo = 0

in this example the memo field is ntext.

What can i do?

0
Comment
Question by:tricks801
  • 4
6 Comments
 
LVL 11

Expert Comment

by:SweatCoder
ID: 11696264
what's the problem? an error?
0
 

Author Comment

by:tricks801
ID: 11696302
ok, the Min was a mistake...but this is found as a subquery, so it is still coming up with the same error
0
 

Author Comment

by:tricks801
ID: 11696307
sorry....the error is stated in the title:  The text, ntext, and image data types are invalid in this subquery or aggregate expression.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:tricks801
ID: 11696321
a very simplified version of the actual query...
select (SELECT (Memo) FROM PmtsMemos WHERE PmtsMemos.InfinityRecNo = 620073)
0
 
LVL 1

Accepted Solution

by:
seangw earned 500 total points
ID: 11696363
You can't select a min or max of a binary type (aggregate functions on ntext, etc.)  if you want to do so, you can try to convert the type to an integer then do the min on it.

This should raise the question on whether you are using the correct datatype here.  If you are always going to have numbers, you are best off correctly classifying the field as int, or float, to speed up the database as well as open up these useful aggregate functions.
0
 

Author Comment

by:tricks801
ID: 11696421
the agg function was a mistake... i fixed it by cast(memo as nvarchar)

thanks
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question