IV47724: UPDATEDB FAILS WITH ORA-01427 WHILE UPDATING LOCMETERREADING READINGDATES

APAR status

• Closed as program error.

Error description

• UpdateDB fails while updating records with locmeterreading
  readingdates
  
  While running the database change script V7500_700.dbc and the
  V7500_700.class file, the following error message is displayed:
  
  java.sql.SQLException: ORA-01427: Subquery on a line delivers
  more than
  one row
  
  Environment :
  Upgrading from 7.1 to 7.5
  In this instance 7.1.1.11 to 7.5.0.4 but may show on any 7.1 to
  7.5 upgrade
  
  
  This issue is similar to APAR IV37355
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Those upgrading from 7.1 to 7.5 who have a multilanguage     *
  * environment and use meters                                   *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * UPDATEDB FAILS IN V7500_700 WITH ERROR INDICATING SUBQUERY   *
  * HAS RETURNED MORE THAN ONE RECORD WHERE ONE RECORD IS        *
  * EXPECTED (ORA-01427 in Oracle)                               *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Replace older version of V7500_700 with one from latest fix  *
  * pack before running updateDB.                                *
  ****************************************************************
  This problem impacts clients who have a multilanguage
  environment that includes locales that use different decimal
  separators, for example, en_US (".") and de_DE (","), and who
  see records
  returned for any of the following 4 SQL statements:
  
  select measuredate,assetnum,metername,siteid,count(assetnum)
  from measurement group by measuredate,assetnum,metername,siteid
  having count(assetnum)>1;
  select measuredate,location,metername,siteid,count(location)
  from measurement group by measuredate,location,metername,siteid
  having count(location)>1;
  select readingdate,assetnum,metername,siteid,count(assetnum)
  from meterreading group by readingdate,assetnum,metername,siteid
  having count(assetnum)>1;
  select readingdate,location,metername,siteid,count(location)
  from locmeterreading group by
  readingdate,location,metername,siteid having count(location)>1;
  
  This defect is in the script V7500_700 created for the 7.1 to
  7.5 upgrade to fix ASSETMETER and LOCATIONMETER LASTREADING
  values. This script
  was needed as a part of the fix for related APAR IZ86152. This
  script assumed that MEASUREMENT.MEASUREMENTDATE,
  METERREADING.READINGDATE and
  LOCMETERREADING.READINGDATE values would be unique for an asset
  or location meter. However, in some instances this was not true,
  leading to the
  failure of this script due to SQL subqueries that return more
  than one reading or measurement where a single record was
  needed.
  

Problem conclusion

• The problem has been addressed by modifying the script
  V7500_700. The script now makes small modifications to
  measurementdate and readingdates (adds milliseconds on supported
  DB platforms, otherwise adds one second to each duplicate date
  in a sequence of duplicate dates for a given meter).	 The
  assumption used is that the larger the unique identifier
  (MEASUREMENT.MEASUREMENTID, METERREADING.METERREADINGID, and
  LOCMETERREADING.METERREADINGID), the more recent the reading is.
  
  The fix for this APAR is contained in the following maintenance
  package:
  		 | release\fix pack\interim fix for Release 7.5.0.6 of Base
  Services
  

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:

Modules/Macros

• MAXIMO
  

Fix information

• Fixed component name

  UPGRADE PROGRAM

• Fixed component ID

  5724R46UP

Applicable component levels

• R750 PSY

     UP