3480821 - SQL Invocation Returns No Results, Causes Stack Trace Error

A stack trace error is seen in the "Exceptions" or "Messages" section of the time sheet. The short text mentions "SQL_INVOCATION", as in the example below.


The full error can be pulled from a System Error Report or the server logs. An example is provided below.

2021-11-15 14:34:02,835 com.workforcesoftware.Misc.ServerErrorLogger ERROR [http-nio-10360-exec-16] [] 20.2.0.95 Server error occurred - debug_error_log id: 1471360876
{ASGNMT_ID=12345679, SQL_INVOCATION_LOADED=true, EMPLOYEE_ID=12345678, DISPLAY_EMPLOYEE=1234, CAUSE=null, SQL_TYPE=2, SYS_VERSION=20.2.0.95, ASGNMT_DESCRIPTION=Job1, EPV=1311559736, LAST_NAME=EMP1, FIRST_NAME=EMP1, FORMULA_PERIOD=2015-01-12, FORMULA_ID=_57B32DB417D251789AF, PP_BEGIN=2015-01-11, SQL_INVOCATION_POLICY=TEST_SQL_INVOCATION2, PP_END=2015-01-24, FORMULA_TXT=var hoursPerDay = callSQL(TEST_SQL_INVOCATION2, EMPLOYEE_ID => employee(day).display_employee).STD_HOURS;

hoursPerDay, FORMULA_PARM_VALUES=Parameter Values: {}, SQL_TEXT=select assignment_std_daily_hours
from asgnmt
where computed_match_id = ?, SQL_INPUT_VALUES={EMPLOYEE_ID=9001}, POLICY_FIELD=HOURS_TO_GENERATE, POLICY_PATH=2015-01-12.POLICY_PROFILE(BI_WEEKLY_01_PPM_TS:1900-01-01-3000-12-31).GENERAL_RULE(BI_WEEKLY_01_GP:1900-01-01-3000-12-31).TRANSACTION_CREATION_POLICYSet(BI_WEEKLY_01_TRANSACTION_CREATION_POLICIES:1900-01-01-3000-12-31).TRANSACTION_CREATION_POLICY(SQL_INVOCATION_TESTER1:1900-01-01-3000-12-31).FORMULA(_57B32DB417D251789AF:1900-01-01-3000-12-31), CALC_STAGE=CREATE_TRANSACTIONS_002, SIMPLE_MSG=SQL invocation: no result found for STD_HOURS, SQL_RESULTS={}}
com.workforcesoftware.Policy.Formula.SqlInvocationException: SQL invocation: no result found for STD_HOURS
	at com.workforcesoftware.Policy.Formula.SqlInvocationManager$Handler.getResult(SqlInvocationManager.java:376)
	at com.workforcesoftware.GenFormula.Formula_57B32DB417D251789AFeff19000101u2s1.eval_hoursPerDay_1(Formula_57B32DB417D251789AFeff19000101u2s1.java:128)
	at com.workforcesoftware.GenFormula.Formula_57B32DB417D251789AFeff19000101u2s1.execCode(Formula_57B32DB417D251789AFeff19000101u2s1.java:108)
	at com.workforcesoftware.GenFormula.Formula_57B32DB417D251789AFeff19000101u2s1.execWithoutTracing(Formula_57B32DB417D251789AFeff19000101u2s1.java:104)
	at com.workforcesoftware.GenFormula.Formula_57B32DB417D251789AFeff19000101u2s1.exec(Formula_57B32DB417D251789AFeff19000101u2s1.java:88)
	at com.workforcesoftware.Policy.Formula.FormulaEvaluator$9.handle_DAY(FormulaEvaluator.java:744)
	at com.workforcesoftware.Gen.Choice.Formula_eval_period.visit(Formula_eval_period.java:160)
	at com.workforcesoftware.Gen.Choice.Formula_eval_period.visit(Formula_eval_period.java:144)
	at com.workforcesoftware.Policy.Formula.FormulaEvaluator.exec(FormulaEvaluator.java:734)
	at com.workforcesoftware.Policy.Formula.FormulaEvaluator.execDouble(FormulaEvaluator.java:638)
	at com.workforcesoftware.Policy.Formula.FormulaEvaluator.calcDouble(FormulaEvaluator.java:189)
	at com.workforcesoftware.Calculation.CreateOtherTransactions$SinglePolicyProcessor.calculateOneFormulaContext(CreateOtherTransactions.java:503)
	at com.workforcesoftware.Calculation.CreateOtherTransactions$SinglePolicyProcessor.calculate(CreateOtherTransactions.java:478)
	at com.workforcesoftware.Calculation.CreateSlices.processOnePolicy(CreateSlices.java:168)
	at com.workforcesoftware.Calculation.CreateOtherTransactions.processOnePolicy(CreateOtherTransactions.java:287)
	at com.workforcesoftware.Calculation.CreateOtherTransactions.processPolicies(CreateOtherTransactions.java:264)
	at com.workforcesoftware.Calculation.CreateOtherTransactions.startDailyPolicies(CreateOtherTransactions.java:250)
	at com.workforcesoftware.Calculation.CreateOtherTransactions.calculate(CreateOtherTransactions.java:72)
	at com.workforcesoftware.Calculation.CalcBase.calculateIfNeeded(CalcBase.java:90)
	at com.workforcesoftware.Data.AllCalcDataManager.runOneCalcStage(AllCalcDataManager.java:1472)
	at com.workforcesoftware.Data.SingleAllCalcDataManager.runCalcStagesAndHandleErrors(SingleAllCalcDataManager.java:2077)
	at com.workforcesoftware.Data.SingleAllCalcDataManager.recalc(SingleAllCalcDataManager.java:2048)
	at com.workforcesoftware.Data.SingleAllCalculationData.recalc(SingleAllCalculationData.java:2551)
	at com.workforcesoftware.Data.AllCalculationData.recalc(AllCalculationData.java:779)
	at com.workforcesoftware.Data.AllCalculationData.recalc(AllCalculationData.java:769)
	at com.workforcesoftware.Data.SingleAllCalcDataManager.recalcPeriodIfStale(SingleAllCalcDataManager.java:2346)
	at com.workforcesoftware.Data.SingleAllCalcDataManager.attemptAllCalculationDataRecalc(SingleAllCalcDataManager.java:379)
	at com.workforcesoftware.Data.SingleAllCalcDataManager.loadAllCalcDataByPeriod(SingleAllCalcDataManager.java:301)
	at com.workforcesoftware.Data.SingleAllCalcDataManager.getLatestAllCalcData(SingleAllCalcDataManager.java:446)
	at com.workforcesoftware.Data.SingleAllCalcDataManager.getAllCalcData(SingleAllCalcDataManager.java:1046)
	at com.workforcesoftware.Data.SingleAllCalcDataManager.getAllCalcData(SingleAllCalcDataManager.java:1052)
	at com.workforcesoftware.TimeEntry.AssignmentManager.getCalculationInfo(AssignmentManager.java:426)
	at com.workforcesoftware.TimeEntry.AssignmentManager.getCalculationInfo(AssignmentManager.java:414)
	at com.workforcesoftware.TimeEntry.TimeEntryManagerImpl.getCalculationInfo(TimeEntryManagerImpl.java:416)
	at com.workforcesoftware.Jsp.TimeEntry.TimeEntryActionHandler.getCalcInfo(TimeEntryActionHandler.java:1897)
	at com.workforcesoftware.Jsp.TimeEntry.TimeEntryActionHandler.updateTimeEntryBean(TimeEntryActionHandler.java:268)
	at com.workforcesoftware.Jsp.TimeEntry.TimeEntryActionHandler.storeTransactionData(TimeEntryActionHandler.java:1731)
	at com.workforcesoftware.Jsp.TimeEntry.TimeEntryActionHandler.saveTransaction(TimeEntryActionHandler.java:1006)
	at com.workforcesoftware.Jsp.TimeEntry.TimeEntryActionHandler.saveTimesheet(TimeEntryActionHandler.java:952)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.workforcesoftware.Jsp.Util.ActionHandler.doAction(ActionHandler.java:228)
	at com.workforcesoftware.ClientRequests.JspUtils.SimpleAction.doAction(SimpleAction.java:51)
	at com.workforcesoftware.ClientRequests.JspUtils.WActionBase.performNoSynchronize(WActionBase.java:741)
	at com.workforcesoftware.ClientRequests.JspUtils.WActionBase.handleNormalRequest(WActionBase.java:599)
	at com.workforcesoftware.ClientRequests.JspUtils.WActionBase.handleRequest(WActionBase.java:329)
	at com.workforcesoftware.ClientRequests.JspUtils.WActionBase.execute(WActionBase.java:191)
	at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431)
	at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236)
	at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
	at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:660)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at com.ezest.util.TargetToSystemFeatureFilter.doFilter(TargetToSystemFeatureFilter.java:125)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at com.workforcesoftware.ClientRequests.WFServletFilter$1.execute(WFServletFilter.java:66)
	at com.workforcesoftware.runtime.ResourceTracker.executeThenRelease(ResourceTracker.java:187)
	at com.workforcesoftware.runtime.ResourceTracker.executeOperationAndReleaseResources(ResourceTracker.java:99)
	at com.workforcesoftware.ClientRequests.WFServletFilter.doFilter(WFServletFilter.java:57)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at com.workforcesoftware.ClientRequests.UIThemeFilter.doFilter(UIThemeFilter.java:58)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at com.workforcesoftware.ClientRequests.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:48)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at com.workforcesoftware.ClientRequests.P3PFilter.doFilter(P3PFilter.java:56)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:543)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:615)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:818)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1627)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:748)

SAP WorkForce Time and Attendance, All Versions 

KBA , XX-PART-WFR-TAM , SAP Time and Attendance Management by Workforce Software , Problem