SUPPORT / SAMPLES & SAS NOTES
Problem Note 70327: You are unable to view the list of cases associated with an entity in SAS® Anti-Money Laundering
 |  |  |  |  |
You might encounter the following error message when you open the Entity Details window to view the list of cases associated with the entity:
The /Lev<n>/Web/Logs/SASServer8_1/SASComplianceSolutionsMid7.1.log file in the mid-tier server might display a similar error. The following example uses the IBM Db2 database:
org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [SELECT COUNT(DISTINCT A.CASE_ID) FROM FSK_CASE_ENTITY B LEFT JOIN FSK_CASE A ON B.CASE_ID=A.CASE_ID WHERE B.ENTITY_LEVEL_CODE = ? AND B.entity_number = ? AND ((CREATE_DATE >= ? or ( A.LSTUPDATE_DATE >= ? and CASE_STATUS_CODE in ( ?,? ) ) ) ) AND (QUEUE_CODE IS NULL OR QUEUE_CODE='' OR QUEUE_CODE IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)) ]; DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null, DRIVER=4.25.1301; nested exception is com.ibm.db2.jcc.am.SqlDataException: DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null, DRIVER=4.25.1301
at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:83)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:83)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
This issue is caused by incorrect SQL query parameter positioning used in sasaml.casesForEntity.list.count.sql.
Workaround
To circumvent this issue, create an override file to override the SQL query for sasaml.casesForEntity.list.count.
For example, here is a query:
sasaml.casesForEntity.list.count.sql=SELECT COUNT(DISTINCT A.CASE_ID) FROM FSK_CASE_ENTITY B LEFT JOIN FSK_CASE A ON B.CASE_ID=A.CASE_ID WHERE B.ENTITY_LEVEL_CODE = ? AND B.%ID_OR_KEY_COLUMN% = ? %FILTERS% %QUEUES% %EMPLOYEE_IND%
Then, here is the corrected query:
sasaml.casesForEntity.list.count.sql=SELECT COUNT(DISTINCT A.CASE_ID) FROM FSK_CASE_ENTITY B LEFT JOIN FSK_CASE A ON B.CASE_ID=A.CASE_ID WHERE B.%ID_OR_KEY_COLUMN% = ? AND B.ENTITY_LEVEL_CODE = ? %FILTERS% %QUEUES% %EMPLOYEE_IND%
Click the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS Anti-Money Laundering (on SAS 9.x) | Microsoft® Windows® for x64 | 7.1 | 9.4 TS1M3 | ||
| 64-bit Enabled AIX | 7.1 | 9.4 TS1M3 | ||||
| 64-bit Enabled Solaris | 7.1 | 9.4 TS1M3 | ||||
| Linux for x64 | 7.1 | 9.4 TS1M3 | ||||