Query with order by clause causes error when opened to add items to reference list

Technote (troubleshooting)


In IBM Rational ClearQuest, a query with an order by clause causes error when opened to add items to a reference list.


If a query with an ORDER BY ASC or DESC clause is used to add items to a reference list, an error occurs. When a browse adds ,T1.id ASC to the query SQL syntax, this error occurs:

IBM Rational ClearQuest
ERROR! CRMDB0001E SQLExecDirect: RETCODE=-1, State=37000, Native Error=169
[Microsoft][ODBC SQL Server Driver][SQL Server]A column has been
specified more than once in the order by list. Columns in the order by list must be unique.
SQL statement='select distinct T1.dbid,T1.id,T1.headline,T2.name,T1.id from request T1,statedef
T2 where T1.state = T2.id and (T1.dbid ?? 0) order by T1.id ASC,T1.id ASC'


The SQL generated to show the results adds a part to the syntax that makes it invalid. Request query SQL syntax:

select distinct T1.dbid,T1.id,T1.headline,T3.name from Defect
T1,statedef T3 where T1.state = T3.id and (T1.dbid ?? 0) order by T1.id ASC

The browse adds ,T1.id ASC to the syntax and causes the error.

This was a product defect under APAR PM85393.


The problem only happens in the ClearQuest Client for Windows, not in the web or Eclipse clients.

Diagnosing the problem

Steps to reproduce the problem:

  • Take two stateful record types, Defect and Request.
  • Open a Defect record add a reference list to the Request field in the ClearQuest windows client
  • Create a query for Request records
  • Sort on id field (DESC or ASC)
  • Modify Defect record, click Add button for Request reference list field
  • Click Browse and select the Request query. Error is thrown

Resolving the problem

To resolve this problem, upgrade to ClearQuest, or

Document information

More support for:

Rational ClearQuest
Client - Native Windows

Software version:,,,,,, 8.0.1

Operating system(s):


Reference #:


Modified date:


Translate my page

Content navigation