BUG: Error 403: UNKNOWN TOKEN on SELECT with Subquery
ID: Q164917
|
The information in this article applies to:
-
Microsoft SQL Server version 6.5
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.
MORE INFORMATION
For additional information, see the following related article in the
Microsoft Knowledge Base:
Q156292
: FIX: Create View with Nested SELECT in CASE Causes Error 206
Additional query words:
coalesce unknown token
Keywords : kbusage SSrvTran_SQL
Version : 6.5
Platform : WINDOWS
Issue type : kbbug