BUG: Error 403: UNKNOWN TOKEN on SELECT with Subquery (164917)
The information in this article applies to:
This article was previously published under Q164917
BUG #: 16554 (Windows NT: 6.5)
SYMPTOMS
Parse error 403 is reported on a SELECT statement when using the COALESCE
function in conjunction with a subquery. This problem has been reproduced
on SQL Server6.5, but the problem does not occur on SQL Server 6.0.
The following is the text of the error reported:
Msg 403, Level 16, State 1
Invalid operator for datatype op: UNKNOWN TOKEN type: varchar
The following query will generate the error message against the pubs
database:
SELECT T1.title_id,
pubid = COALESCE((select T3.pub_id from publishers T3
where T3.pub_id = T1.pub_id and
T3.pub_id = T2.pub_id),
(select T3.pub_id from publishers T3
where T3.pub_id = T1.pub_id and
T3.pub_id = T2.pub_id),
'BU1032')
FROM Titles T1, Titles T2
WHERE T1.title_id = 'BU1032'
WORKAROUND
To work around this problem, do either of the following:
- Use CASE...IS NOT NULL Transact-SQL statements, as shown in the
following example:
SELECT T1.title_id,
pubid=
CASE
WHEN
(select T3.pub_id from publishers T3
where T3.pub_id = T1.pub_id and
T3.pub_id = T2.pub_id)
IS NOT NULL THEN
(select T3.pub_id from publishers T3
where T3.pub_id = T1.pub_id and
T3.pub_id = T2.pub_id)
WHEN
(select T3.pub_id from publishers T3
where T3.pub_id = T1.pub_id and
T3.pub_id = T2.pub_id)
IS NOT NULL THEN
(select T3.pub_id from publishers T3
where T3.pub_id = T1.pub_id and
T3.pub_id = T2.pub_id)
ELSE
'BU1032'
END
FROM Titles T1, Titles T2
WHERE T1.title_id = 'BU1032'
-OR-
- Use the convert function to tell SQL Server that the result of the
subselect is the same type as the assignment column.
For example, you can use a query similar to the following the
following:
SELECT T1.title_id,
pubid = COALESCE(convert(varchar,(select T3.pub_id from publishers T3
where T3.pub_id = T1.pub_id and
T3.pub_id = T2.pub_id)),
'BU1032')
FROM Titles T1, Titles T2
WHERE T1.title_id = 'BU1032'
STATUS
Microsoft has confirmed this to be a problem in Microsoft SQL Server
version 6.5. We are researching this problem and will post new information
here in the Microsoft Knowledge Base as it becomes available.
| Modification Type: | Major | Last Reviewed: | 10/3/2003 |
|---|
| Keywords: | kbbug kbusage KB164917 |
|---|
|