Microsoft KB Archive/164917

= BUG: Error 403: UNKNOWN TOKEN on SELECT with Subquery =

Article ID: 164917

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



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.



MORE INFORMATION
For additional information, see the following related article in the Microsoft Knowledge Base:

156292 : FIX: Create View with Nested SELECT in CASE Causes Error 206

Additional query words: coalesce unknown token

Keywords: kbbug kbusage KB164917

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.