IBM Support

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 , 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.headline,, from request T1,statedef
T2 where T1.state = and (T1.dbid ?? 0) order by ASC, 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.headline, from Defect
T1,statedef T3 where T1.state = and (T1.dbid ?? 0) order by ASC

The browse adds , 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): Windows

Reference #: 1651689

Modified date: 04 October 2013

Translate this page: