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


Last Reviewed: April 9, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.