BUG: Error 403: UNKNOWN TOKEN on SELECT with Subquery

• Microsoft SQL Server version 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

   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