IBM Support

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression" when trying to lock/unlock any period (in "Change-Period locking") caused by APAR PH12550



User clicks "Maintain - Period locking - Change-Period locking". User selects any period, and locks (or unlocks) one of the companies in the list.
  • User clicks 'Save' (to save changes). An error appears.
  • If user tries to perform the same task (lock/unlock periods) in Controller Web, a different error appears.
The problem may appear to be intermittent. This is because it is triggered only after the status of a company has changed (for example by running a 'company reconcile' process).


Controller Classic:
Standard Error
Number:    5
Source:    FrangoDirect.PerLockHandler.PushPeriodLocks#System.Web.Services
Description:    System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Exception: The statement has been terminated.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
   at Microsoft.VisualBasic.ErrObject.Raise(Int32 Number, Object Source, Object Description, Object HelpFile, Object HelpContext)
   at ControllerServerCommon.RaiseErrSrv.RaiseError(String sUser, Int32 lErrNo, String sErrSource, String sErrDesc, String sErrHelpFile, Int32 lErrHelpContext)
   at FrStatusBT.PerLockBusinessT.PushPeriodLocks(String sGuid, String sUser, RecordSet rsPerlock, String sPerLock, Connection& conCaller, Boolean bUseTransaction, Boolean bSetStatus)
   at Cognos.Controller.Proxy.CCRWS.PerLockBusinessT_PushPeriodLocks(String sGuid, String sUser, DataSet rsPerlock, String sPerLock, Object& conCaller, Boolean bUseTransaction, Boolean bSetStatus)
   --- End of inner exception stack trace ---
   at Microsoft.VisualBasic.CompilerServices.Symbols.Container.InvokeMethod(Method TargetProcedure, Object[] Arguments, Boolean[] CopyBack, BindingFlags Flags)
   at Microsoft.VisualBasic.CompilerServices.NewLateBinding.CallMethod(Container BaseReference, String MethodName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, BindingFlags InvocationFlags, Boolean ReportErrors, ResolutionFailure& Failure)
   at Microsoft.VisualBasic.CompilerServices.NewLateBinding.ObjectLateCall(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean IgnoreReturn)
   at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean IgnoreReturn)
   at Cognos.Controller.Forms.Form.frmPerLockComp.IFile_DoSave()
Controller Web:
Error while changing lock status for company xxxxxxxxxxxxxxxxx
Unexpected error


There are several known causes for similar errors.
  • TIP: See separate IBM Technote #0960392 for more examples.
This Technote specifically relates to the scenario where the cause is a defect (reference APAR PH12550) in the following versions of Controller:
  • Controller 10.4.0 RTM (
  • Controller 10.4.0 IF1 (
This causes corrupt values in temporary database tables.
More Information:
The defect was introduced in 10.4.0 onwards (as part of the new functionality where there is an audit trail of period locking and company locking)
  • It causes the system audit log table "sactrlinfo" to not be cleared (after Company status is changed).
For example, a method to trigger the problem is:
1. User #1 changes the status of a company
  • There are many different ways of doing this, for example by running Company Reconcile
2. User #2 tries to lock/unlock the period for that specific company.
  • Error occurs


Both of the following are true:
  • Controller 10.4.0 (not any earlier version, for example 10.3.1)
  • The customer has the Controller 'system audit log' functionality enabled.

Diagnosing The Problem

Open the database table "sactrlinfo". Inside there will be some entries which are causing the issue.
systempart    controlleruser    sourcename    changedate
COMPANY_PERIOD_LOCKING    USER1    Locked_Companies    2019-06-10 14:01:25.093
COMPANY_PERIOD_LOCKING    USER3    Locked_Companies    2019-06-06 15:15:59.387
COMPANY_PERIOD_LOCKING    USER4    SetCompanyReady    2019-06-07 13:48:17.250
COMPANY_PERIOD_LOCKING    USER5    Locked_Companies    2019-06-10 09:53:31.307
COMPANY_PERIOD_LOCKING    USER6    Locked_Companies    2019-06-05 15:15:09.930
COMPANY_PERIOD_LOCKING    USER7    Locked_Companies    2019-06-10 10:20:15.303
COMPANY_PERIOD_LOCKING    USER8    SetCompanyReady    2019-06-07 15:50:45.540
COMPANY_PERIOD_LOCKING    USER9    SetCompanyReady    2019-06-10 14:29:39.560
COMPANY_PERIOD_LOCKING    USER10    Locked_Companies    2019-06-10 08:47:13.073
COMPANY_PERIOD_LOCKING    USER11    Locked_Companies    2019-06-10 08:50:59.597
COMPANY_PERIOD_LOCKING    USER12    Locked_Companies    2019-06-10 13:26:30.100
COMPANY_PERIOD_LOCKING    USER13    Locked_Companies    2019-06-06 17:05:55.307
COMPANY_PERIOD_LOCKING    USER14    SetCompanyReady    2019-06-06 09:50:06.987
COMPANY_PERIOD_LOCKING    USER15    SetCompanyReady    2019-06-07 13:05:49.590
COMPANY_PERIOD_LOCKING    USER16    SetCompanyReady    2019-06-07 16:54:36.760
COMPANY_PERIOD_LOCKING    USER17    Locked_Companies    2019-06-10 14:27:09.250
COMPANY_PERIOD_LOCKING    USER18    Locked_Companies    2019-06-10 12:59:12.270

Resolving The Problem

Upgrade to either:
  • Controller 10.4.0 IF2 ( or a later version of Controller 10.4.0
  • or Controller 10.4.1 (or later).
There are several different methods to workaround the problem.
Method #1 (instant cure of symptom, but issue will likely return later)
Delete the entire contents of the temporary table 'sactrlinfo'.
There are two ways to achieve this:
  • either (a) Ask your I.T. department's database administrator (DBA) to delete the contents of the temporary table 'sactrlinfo' (do not delete the actual table itself!).
  • or (b) Perform a database optimisation.
Steps to perform a database optimisation:
1. Ensure no other users logged onto Controller
2. Click "Maintain - User - Single User"
3. Click "Maintain - Database - Optimize"
4. Choose/select all available options *except* do not tick 'Rebuild indexes' (there is no need - this option is for performance improvement reasons)
5. Click "Run".
Method #2
Disable the system audit log functionality entirely.
Method #3 (ideal long-term workaround)
Disable the system audit log functionality only in relation to the locking/unlocking of companies and periods.
IMPORTANT: Be aware that doing this will break the following two functions:
  • Controller Web: Real time update functionality for company lock statuses (in the Controller Web dashboard).
    • If you are not using Controller Web, then you do not have to worry about this!
  • Controller Classic: he new "auditing on company locking" feature that was first introduced in 10.4
    • If you have recently upgraded from 10.3.1, you may not be using this feature anyway.
Steps to disable audit log for locking/unlocking of companies/periods:
Ask your I.T. department's database administrator (SQL DBA) to:
1. Locate the Controller database
2. Expand the table: xopen
3. Disable the trigger: trg_saxxopen

Document Location


[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"Component":"","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.4.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
21 January 2020