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

Technote (troubleshooting)


Problem(Abstract)

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

Symptom

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'
---------------------------
OK
---------------------------

Cause

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.


Environment

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 7.1.2.12, 8.0.0.8 or 8.0.1.1.

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

Rational ClearQuest
Client - Native Windows

Software version:

7.1.2.1, 7.1.2.10, 7.1.2.11, 8.0.0.5, 8.0.0.6, 8.0.0.7, 8.0.1

Operating system(s):

Windows

Reference #:

1651689

Modified date:

2013-10-04

Translate my page

Machine Translation

Content navigation