BUG: Error 403: UNKNOWN TOKEN on SELECT with Subquery

Retired KB Content Disclaimer

View products that this article applies to.

This article was previously published under Q164917

BUG #: 16554 (Windows NT: 6.5)

Expand all | Collapse all

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'

Back to the top

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'

Back to the top

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.

Back to the top

MORE INFORMATION

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

156292 (http://support.microsoft.com/kb/156292/EN-US/ ) : FIX: Create View with Nested SELECT in CASE Causes Error 206

Back to the top

APPLIES TO

Microsoft SQL Server 6.5 Standard Edition

Back to the top

kbbug kbusage KB164917

Back to the top

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top