SQL Server help, Error 279: The text, ntext, and image..
08-02-2001, 12:04 PM
#1
Member
Thread Starter
Join Date: Jun 2001
Posts: 2,058
Likes: 0
Received 0 Likes
on
0 Posts
SQL Server help, Error 279: The text, ntext, and image..
data types are invalid in this subquery or aggregate function.
Is there any way around this error???
Heres my SQL Statement.
NOTE: data in columns are declared as ntext
declare @variable as sysname
Update [User_table]
set [User_table.Field_name] = (select case @variable
when 'Field_name1' then Field_name1
when 'Field_name2' then Field_name2
when 'Field_name3' then Field_name3
end as Response1
from [System_table]
where System_table.primekey = @primekey)
where User_table.primekey = @primekey and User_table.userid = @Userid
Is there any way around this error???
Heres my SQL Statement.
NOTE: data in columns are declared as ntext
declare @variable as sysname
Update [User_table]
set [User_table.Field_name] = (select case @variable
when 'Field_name1' then Field_name1
when 'Field_name2' then Field_name2
when 'Field_name3' then Field_name3
end as Response1
from [System_table]
where System_table.primekey = @primekey)
where User_table.primekey = @primekey and User_table.userid = @Userid
08-02-2001, 12:16 PM
#3
Senior Member
Join Date: Jun 2000
Posts: 7,903
Likes: 0
Received 0 Likes
on
0 Posts
I don't think so... it's common among rdbs not to allow agg. functions or subquerys
on large fields, ie ntext image etc...
Think about it, ntext can be up to 16gb in length I think, imagine what that would do when putting into a variable.
Think about it, ntext can be up to 16gb in length I think, imagine what that would do when putting into a variable.
08-02-2001, 12:44 PM
#4
New Member
Join Date: Feb 2001
Posts: 414
Likes: 0
Received 0 Likes
on
0 Posts
He is correct, such large datatypes are not allowed to be used in aggregates.
I got into the same problem using TEXT. You might be able to convert, but I dont remember.
Nice to see a fellow DB'r on the board ;-) Good luck.
Nice to see a fellow DB'r on the board ;-) Good luck.
08-02-2001, 01:03 PM
#5
AudiWorld Super User
Join Date: Nov 2000
Posts: 17,618
Likes: 0
Received 0 Likes
on
0 Posts
subquery rules
From MS SQL Server BOL:
Subquery Rules
A subquery is subject to a number of restrictions:
The select list of a subquery introduced with a comparison operator can include only one expression or column name (except that EXISTS and IN operate on SELECT * or a list, respectively).
If the WHERE clause of an outer query includes a column name, it must be join-compatible with the column in the subquery select list.
The ntext, text and image data types are not allowed in the select list of subqueries.
Because they must return a single value, subqueries introduced by an unmodified comparison operator (one not followed by the keyword ANY or ALL) cannot include GROUP BY and HAVING clauses.
The DISTINCT keyword cannot be used with subqueries that include GROUP BY.
The COMPUTE and INTO clauses cannot be specified.
ORDER BY can only be specified if TOP is also specified.
A view created with a subquery cannot be updated.
The select list of a subquery introduced with EXISTS by convention consists of an asterisk (*) instead of a single column name. The rules for a subquery introduced with EXISTS are identical to those for a standard select list because a subquery introduced with EXISTS constitutes an existence test and returns TRUE or FALSE, rather than data.
Subquery Rules
A subquery is subject to a number of restrictions:
The select list of a subquery introduced with a comparison operator can include only one expression or column name (except that EXISTS and IN operate on SELECT * or a list, respectively).
If the WHERE clause of an outer query includes a column name, it must be join-compatible with the column in the subquery select list.
The ntext, text and image data types are not allowed in the select list of subqueries.
Because they must return a single value, subqueries introduced by an unmodified comparison operator (one not followed by the keyword ANY or ALL) cannot include GROUP BY and HAVING clauses.
The DISTINCT keyword cannot be used with subqueries that include GROUP BY.
The COMPUTE and INTO clauses cannot be specified.
ORDER BY can only be specified if TOP is also specified.
A view created with a subquery cannot be updated.
The select list of a subquery introduced with EXISTS by convention consists of an asterisk (*) instead of a single column name. The rules for a subquery introduced with EXISTS are identical to those for a standard select list because a subquery introduced with EXISTS constitutes an existence test and returns TRUE or FALSE, rather than data.
Thread
Thread Starter
Forum
Replies
Last Post
Dclueless1
Audi A5 / S5 / RS5 Coupe & Cabrio (B8)
7
06-03-2010 03:46 PM
