APAR status
Closed as program error.
Error description
When the client uses this query in a SQL Server environment: historyflag=0 and istask=0 and status='APPR' and 'WMATL' = (select top 1 status from wostatus where wonum=workorder.wonum and siteid=workorder.siteid and changedate<workorder.statusdate order by changedate desc) and statusdate > dateadd(day,-3,getdate()) It works correctly in WOTrack but it does not work when used as a query for a Start Center Result Set. When used on a result set is produces this error: BMXAA4210E - Database error number 102 has occurred. Invalid SQL queries are logged in the log file. Check the SQL query to identify the error. I have reproduced it on 7.6.0.4, the client is on 7.5.0.7. This exception is in the log. Ask me for the full log. You can see below it cuts off the query above at the Order By. [3/2/16 17:19:25:017 EST] 0000dd50 SystemOut O 02 Mar 2016 17:19:25:017 [INFO] [MXServer-SQS-CI-y4L3rP] [] BMXAA6719I - USER = (WILSON) SPID = (90) app (WOTRACK) object (WORKORDER) : select count(*) from workorder where historyflag=0 and istask=0 and status='APPR' and 'WMATL' = (select top 1 status from wostatus where wonum=workorder.wonum and siteid=workorder.siteid and changedate<workorder.statusdate [3/2/16 17:19:25:017 EST] 0000dd50 SystemOut O 02 Mar 2016 17:19:25:017 [ERROR] [MXServer-SQS-CI-y4L3rP] [] select count(*) from workorder where historyflag=0 and istask=0 and status='APPR' and 'WMATL' = (select top 1 status from wostatus where wonum=workorder.wonum and siteid=workorder.siteid and changedate<workorder.statusdate com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'statusdate'. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabase Error(SQ LServerException.java:216) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQ LServerS tatement.java:1515) at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteCursore d(SQLSer verStatement.java:1879) at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStateme nt(SQLSe rverStatement.java:766) at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doEx ecute(SQ LServerStatement.java:689) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:56 96) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand( SQLServe rConnection.java:1715) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(S QLServer Statement.java:180) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement (SQLServ erStatement.java:155) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQL ServerSt atement.java:616) at psdi.mbo.MboSet.getDBValue(MboSet.java:4204) at psdi.mbo.MboSet.getDBValue(MboSet.java:4045) at psdi.mbo.MboSet.getDBCalc(MboSet.java:4274) at psdi.mbo.MboSet.count(MboSet.java:3634) STEPS TO REPRODUCE: 1. In a SQL Server environment put in this query in WOTrack. You can do this on the List tab by dropping down the Advanced Search menu and picking Where Clause. Then paste this in: historyflag=0 and istask=0 and status='APPR' and 'WMATL' = (select top 1 status from wostatus where wonum=workorder.wonum and siteid=workorder.siteid and changedate<workorder.statusdate order by changedate desc) and statusdate > dateadd(day,-3,getdate()) 2. You can then Save the Query. When you execute it, you may or may not get any data but you do not get an error. 3. Now go to the Start Center and add a Portlet of Type Result Set. Give it the query you saved in Step 2. 4. When you display the Start Center you will now get the error: The portlet cannot open. BMXAA4210E - Database error number 102 has occurred. Invalid SQL queries are logged in the log file. Check the SQL query to identify the error.
Local fix
(none)
Problem summary
**************************************************************** * USERS AFFECTED: * * All Maximo Users * **************************************************************** * PROBLEM DESCRIPTION: * * Query with order by clause in sub-query works in wotrack but * * doesn't work when used in a start center result set with a * * sql server database. * **************************************************************** * RECOMMENDATION: * * Upgrade to latest release * **************************************************************** When the client uses this query in a SQL Server environment: historyflag=0 and istask=0 and status='APPR' and 'WMATL' = (select top 1 status from wostatus where wonum=workorder.wonum and siteid=workorder.siteid and changedate<workorder.statusdate order by changedate desc) and statusdate > dateadd(day,-3,getdate()) It works correctly in WOTrack but it does not work when used as a query for a Start Center Result Set. When used on a result set is produces this error: BMXAA4210E - Database error number 102 has occurred. Invalid SQL queries are logged in the log file. Check the SQL query to identify the error. This exception is in the log. You can see below it cuts off the query above at the Order By. BMXAA6719I - USER = (WILSON) SPID = (90) app (WOTRACK) object (WORKORDER) : select count(*) from workorder where historyflag=0 and istask=0 and status='APPR' and 'WMATL' = (select top 1 status from wostatus where wonum=workorder.wonum and siteid=workorder.siteid and changedate<workorder.statusdate select count(*) from workorder where historyflag=0 and istask=0 and status='APPR' and 'WMATL' = (select top 1 status from wostatus where wonum=workorder.wonum and siteid=workorder.siteid and changedate<workorder.statusdate com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'statusdate'. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabase Error(SQLServerException.java:216) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQ LServerStatement.java:1515) at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteCursore d(SQLServerStatement.java:1879) at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStateme nt(SQLServerStatement.java:766) at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doEx ecute(SQLServerStatement.java:689) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:56 96) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand( SQLServerConnection.java:1715) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(S QLServerStatement.java:180) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement (SQLServerStatement.java:155) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQL ServerStatement.java:616) at psdi.mbo.MboSet.getDBValue(MboSet.java:4204) at psdi.mbo.MboSet.getDBValue(MboSet.java:4045) at psdi.mbo.MboSet.getDBCalc(MboSet.java:4274) at psdi.mbo.MboSet.count(MboSet.java:3634) STEPS TO REPRODUCE: 1. In a SQL Server environment put in this query in WOTrack. You can do this on the List tab by dropping down the Advanced Search menu and picking Where Clause. Then paste this in: historyflag=0 and istask=0 and status='APPR' and 'WMATL' = (select top 1 status from wostatus where wonum=workorder.wonum and siteid=workorder.siteid and changedate<workorder.statusdate order by changedate desc) and statusdate > dateadd(day,-3,getdate()) 2. You can then Save the Query. When you execute it, you may or may not get any data but you do not get an error. 3. Now go to the Start Center and add a Portlet of Type Result Set. Give it the query you saved in Step 2. 4. When you display the Start Center you will now get the error: The portlet cannot open. BMXAA4210E - Database error number 102 has occurred. Invalid SQL queries are logged in the log file. Check the SQL query to identify the error.
Problem conclusion
Query with order by clause in sub-query works when used in a start center result set with a sql server database. The fix for this APAR is contained in the following maintenance package: | release\fix pack | Fix Pack Release 7.5.0.11 TPAE
Temporary fix
Comments
APAR Information
APAR number
IV83052
Reported component name
WORK & MTRL APP
Reported component ID
TIVOWRKMM
Reported release
750
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2016-03-25
Closed date
2016-04-26
Last modified date
2024-10-29
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
WORK & MTRL APP
Fixed component ID
TIVOWRKMM
Applicable component levels
[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSLKT6","label":"Maximo Asset Management"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"750","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]
Document Information
Modified date:
31 October 2024