PI34244: 'ORA-01722: INVALID NUMBER' ON SEARCH BY ID IN CQ 8.0.1.6

Rational ClearQuest Fix Pack 8 (8.0.1.8) for 8.0.1
Rational ClearQuest Fix Pack 9 (8.0.1.9) for 8.0.1
Rational ClearQuest Fix Pack 10 (8.0.1.10) for 8.0.1
Rational ClearQuest Fix Pack 11 (8.0.1.11) for 8.0.1
Rational ClearQuest Fix Pack 12 (8.0.1.12) for 8.0.1
Rational ClearQuest Fix Pack 13 (8.0.1.13) for 8.0.1
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
Rational ClearQuest Fix Pack 20 (8.0.1.20) for 8.0.1
Rational ClearQuest Fix Pack 21 (8.0.1.21) for 8.0.1
Rational ClearQuest Fix Pack 22 (8.0.1.22) for 8.0.1
Rational ClearQuest Fix Pack 23 (8.0.1.23) for 8.0.1
Rational ClearQuest Fix Pack 24 (8.0.1.24) for 8.0.1
Rational ClearQuest Fix Pack 25 (8.0.1.25) for 8.0.1

APAR status

• Closed as program error.

Error description

• IBM Rational ClearQuest (CQ) Web a Search by ID may throw an
  'ORA-01722: invalid number' error for a search on an existing
  stateless record id.
  
  If the record id doesn't start with the db name as it does for
  statefull records (e.g.SAMPL00000009), the search loops through
  all record-type tables to find a match.
  
  In 8.0.1.6 it breaks with following error when the loop reaches
  a stateless record type that has a composite primary key (PK) of
  dbid and a string field:
  
  Error from RPC server: Failed to find the record: CRMDB0001E
  SQLExecDirect: RETCODE=-1, State=22005, Native Error=1722
  [IBM Rational DataDirect][ODBC Oracle Wire Protocol
  driver][Oracle]ORA-01722: invalid number SQL statement="select
  T1.dbid, T1.is_active, {fn concat({ fn rtrim( {fn
  convert(T1.dbid,sql_char)} )}, {fn concat(' ', T1.name)})} from
  cc_vob_object T1 where T1.dbid ='SAM0000000002' and T1.dbid > 0"
  
  Both Eclipse RCP and  CQ for Windows client will also fail to
  find the existing record.
  Edit -> Find Record with the 'Search All Record Types' option,
  results in error:
  ---------------------------
  Invalid ID SAM0000000002 specified. Please try again.
  ---------------------------
  
  The problem also occurs with the db on SQL Server, here the
  error is:
  
  CRVSV0078E Error from RPC server: CRVSV0985E Failed to find the
  record: CRMDB0001E SQLExecDirect: RETCODE=-1, State=22005,
  Native Error=245 [Microsoft][ODBC SQL Server Driver][SQL
  Server]Conversion failed when converting the varchar value
  'SAM0000000002' to data type int. SQL statement="select T1.dbid,
  T1.is_active, {fn convert(T1.dbid,sql_varchar)} + ' ' + T1.name
  from product T1 where T1.dbid = 'SAM0000000002' and T1.dbid > 0"
  
  
  --------
  Steps to reproduce the problem with a DefectTracking SAMPLE db:
  
  Create two stateless record types: Product and Company
  Product field: Name
  Set PK to be composite: dbid + Name
  
  Company field: Name (= PK)
  
  Create some records for these types.
  A search by ID for an existing Company record name should show
  the error.
  --------
  
  The Search by ID worked correctly in 8.0.1.5
  

Local fix

• Create and run a query (or dynamic query) to find this record in
  the meantime.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * ClearQuest Web                                               *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * The ClearQuest Web a Search by ID feature may throw an       *
  * 'ORA-01722: invalid number' error message when searching for *
  * existing stateless record ids.                               *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  

Problem conclusion

• A fix is available in ClearQuest 8.0.1.8
  When searching for existing stateless record ids, ClearQuest Web
  Search by ID no longer throws the mentioned error message.
  

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

• R801 PSY

     UP