"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

Troubleshooting

Problem

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).

Symptom

Controller Classic:

Standard Error

Details:

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

Cause

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 (10.4.0.103)
Controller 10.4.0 IF1 (10.4.0.105)

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

Environment

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.

Example:

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

Fix:

Upgrade to either:

Controller 10.4.0 IF2 (10.4.0.107) or a later version of Controller 10.4.0
or Controller 10.4.1 (or later).

Workarounds:

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

Related Information

APAR PH12550 - Subquery returned more than 1 value. This is not permitted when …

0960392 - ** Troubleshooting ** "The statement has been terminated. Subquery re…

Document Location

Worldwide

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"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"}}]

Tips