Status 500 Error: AdminService#getDeptListById() - ORA-01427: Single-row Subquery Returns More Than One Row

Status 500 Error: AdminService#getDeptListById() - ORA-01427: Single-row Subquery Returns More Than One Row

This error indicates a problem with a database query executed by the AdminService#getDeptListById() method. Specifically, the error is caused by an ORA-01427 exception, meaning a single-row subquery returned multiple rows.

The potential source of the error lies within the UserMapper.xml file, possibly related to the defaultParameterMap configuration. The problematic SQL statement is:

select ud.DEPT_ID AS 'deptId', dt.DEPT_CODE as 'deptCode', dt.DEPT_NAME AS 'deptName' from SYS_USER_DEPT ud,SYS_DEPT_DICT dt WHERE ud.DEPT_ID = dt.DEPT_ID and ( dt.CLINIC_LABEL_ID is null or dt.DEPT_CODE = (select DATA_VALUE from SYSMN.MAV_EMR_CORE_DICT where BASICDATAVALUE_ID = dt.CLINIC_LABEL_ID) ) and ud.USER_ID =? ORDER BY (select a.sort_id from sysmn.SYS_USER_DEPT_SORT a where a.USER_ID =? and a.CATALOG_LOCATION = '1' and a.DEPT_ID = dt.DEPT_ID) asc

This error can be resolved by:

1. Inspecting the subquery: Ensure that the subquery is designed to return only one row, as the error suggests it's returning multiple. You might need to modify the query conditions or add appropriate limiting clauses.
2. Analyzing the UserMapper.xml: Carefully review the UserMapper.xml file for any issues with the defaultParameterMap or other configurations that might affect the query execution.
3. Debugging the application: Use a debugger or logging to step through the code and pinpoint the exact location where the error occurs. This will help identify potential issues in parameter binding or query construction.

By addressing these points, you can effectively troubleshoot and resolve the ORA-01427 error and restore the functionality of your application.