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

Rational ClearQuest Fix Pack 12 (7.1.2.12) for 7.1.2
Rational ClearQuest Fix Pack 8 (8.0.0.8) for 8.0
Rational ClearQuest Fix Pack 1 (8.0.1.1) for 8.0.1
Rational ClearQuest Fix Pack 14 (7.1.2.14) for 7.1.2
Rational ClearQuest Fix Pack 11 (8.0.0.11) for 8.0
Rational ClearQuest Fix Pack 4 (8.0.1.4) for 8.0.1
Rational ClearQuest Fix Pack 5 (8.0.1.5) for 8.0.1
Rational ClearQuest Fix Pack 12 (8.0.0.12) for 8.0
Rational ClearQuest Fix Pack 15 (7.1.2.15) for 7.1.2
Rational ClearQuest Fix Pack 6 (8.0.1.6) for 8.0.1
Rational ClearQuest Fix Pack 13 (8.0.0.13) for 8.0
Rational ClearQuest Fix Pack 16 (7.1.2.16) for 7.1.2
Rational ClearQuest Fix Pack 17 (7.1.2.17) for 7.1.2
Rational ClearQuest Fix Pack 14 (8.0.0.14) for 8.0
Rational ClearQuest Fix Pack 7 (8.0.1.7) for 8.0.1
Rational ClearQuest Fix Pack 18 (7.1.2.18) for 7.1.2
Rational ClearQuest Fix Pack 15 (8.0.0.15) for 8.0
Rational ClearQuest Fix Pack 8 (8.0.1.8) for 8.0.1
Rational ClearQuest Fix Pack 19 (7.1.2.19) for 7.1.2
Rational ClearQuest Fix Pack 16 (8.0.0.16) for 8.0
Rational ClearQuest Fix Pack 9 (8.0.1.9) for 8.0.1
Rational ClearQuest Fix Pack 17 (8.0.0.17) for 8.0
Rational ClearQuest Fix Pack 10 (8.0.1.10) for 8.0.1
Rational ClearQuest Fix Pack 18 (8.0.0.18) for 8.0
Rational ClearQuest Fix Pack 11 (8.0.1.11) for 8.0.1
Rational ClearQuest Fix Pack 19 (8.0.0.19) for 8.0
Rational ClearQuest Fix Pack 12 (8.0.1.12) for 8.0.1
Rational ClearQuest Fix Pack 20 (8.0.0.20) for 8.0
Rational ClearQuest Fix Pack 13 (8.0.1.13) for 8.0.1
Rational ClearQuest Fix Pack 21 (8.0.0.21) for 8.0
Rational ClearQuest Fix Pack 14 (8.0.1.14) for 8.0.1
Rational ClearQuest Fix Pack 15 (8.0.1.15) for 8.0.1
Rational ClearQuest Fix Pack 16 (8.0.1.16) for 8.0.1
Rational ClearQuest Fix Pack 17 (8.0.1.17) for 8.0.1
Rational ClearQuest Fix Pack 18 (8.0.1.18) for 8.0.1
Rational ClearQuest Fix Pack 19 (8.0.1.19) for 8.0.1

APAR status

• Closed as program error.

Error description

• If a query that has an ORDER BY clause (ASC or DESC) is used to
  add items to a reference list an error is thrown because the SQL
  generated to show the results adds a part to the syntax
  that makes it invalid.
  
  Steps to reproduce the problem.
  
  - 2 stateful record types, Defect and Request.
  - On Defect record add a reference list to Request.
  CQ 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.
  
  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 this
  error:
  
  ---------------------------
  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
  ---------------------------
  Problem happens only in CQ client for windows, not in web or
  eclipse.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  In the ClearQuest Windows client, queries that use an ORDER
  BY clause (ASC or DESC) to add items to a reference list,
  result in an error message that blocks the user from seeing
  the query result set. The error message says "A column has
  been specified more than once in the order by list. Columns
  in the order by list must be unique."
  

Problem conclusion

• A fix is available in ClearQuest 7.1.2.12, 8.0.0.8 and
  8.0.1.1.
  
  ClearQuest Windows client users are able to use a query with
  ORDER BY clause (ASC or DESC) to add items to a reference
  list.  This is now consistent with the ClearQuest Web and
  Eclipse clients.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  CLEARQUEST WIN

• Fixed component ID

  5724G3600

Applicable component levels

• R800 PSN

     UP