I have a report which pulls data on a table VIEW. This view currently has a complicated SQL below to display the data.
SELECT TRANS.ID AS ID,(SELECT PROJECT.PROJECT_COMPANY_REFERENCE_ID FROM CAS_WORKDAY_PROJECT PROJECT WHERE POLICY.PROJECT_ID = PROJECT.ID) AS COMPANY,(SELECT PROJECT.PROJECT_CUSTOMER_REFERENCE_ID FROM CAS_WORKDAY_PROJECT PROJECT WHERE POLICY.PROJECT_ID = PROJECT.ID) AS CLIENT_ID,(SELECT CUSTOMER.CUSTOMER_NAME FROM CAS_WORKDAY_CUSTOMER CUSTOMER, CAS_WORKDAY_PROJECT PROJ WHERE POLICY.PROJECT_ID = PROJ.ID AND PROJ.PROJECT_CUSTOMER_REFERENCE_ID = CUSTOMER.CUSTOMER_ID) AS CLIENT,(SELECT PROJECT.PROJECT_REFERENCE_ID FROM CAS_WORKDAY_PROJECT PROJECT WHERE POLICY.PROJECT_ID = PROJECT.ID) AS PROJECT_ID,(SELECT PROJECT.PROJECT_NAME FROM CAS_WORKDAY_PROJECT PROJECT WHERE POLICY.PROJECT_ID = PROJECT.ID) AS PROJECT,(SELECT TASK.PROJECT_TASK FROM CAS_WORKDAY_TASK TASK WHERE POLICY.TASK_ID = TASK.ID) AS ACTIVITY_ID,(SELECT TASK.PROJECT_PLAN_TASK_CUSTOM_NAME FROM CAS_WORKDAY_TASK TASK WHERE POLICY.TASK_ID = TASK.ID) AS ACTIVITY,(SELECT PROJECT.PROJECT_COST_CENTER_REFERENCE_ID FROM CAS_WORKDAY_PROJECT PROJECT WHERE POLICY.PROJECT_ID = PROJECT.ID) AS COST_CENTER,TRANS.POLICY_ID AS POLICY_ID,POLICY.CARRIER_POLICY_ID AS CARRIER_POLICY,(SELECT REF.REFERENCE_VALUE FROM CAS_REF_REFERENCE_DATA REF WHERE POLICY.PRACTICE_ID = REF.REFERENCE_ID) AS PRACTICE,(SELECT CARRIERS.NAME FROM CAS_REF_PAYEE_AND_CARRIER CARRIERS WHERE CARRIERS.ID = POLICY.CARRIER_CODE_ID) AS CARRIER,(SELECT REF.REFERENCE_VALUE FROM CAS_REF_REFERENCE_DATA REF WHERE POLICY.COVERAGE_CODE_ID = REF.REFERENCE_ID) AS COVERAGE_CODE,POLICY.POLICY_EFFECTIVE_DATE AS POLICY_EFFECTIVE_DATE,POLICY.EXPIRATION_DATE AS EXPIRATION_DATE,TRANS.TRANSACTION_DATE AS TRANS_DATE,TRANS.ACCOUNTING_DATE AS ACCOUNTING_DATE,IF((TRANS.ALLOCATION_TYPE_ID = '9' AND TRANS.RECORD_TYPE_ID = '1'), TRANS.TRANSACTION_AMOUNT, 0.00) AS ACCRUAL_AMOUNT,IF(TRANS.RECORD_TYPE_ID = '3', TRANS.TRANSACTION_AMOUNT, 0.00) AS CASH_AMOUNT,IF(TRANS.RECORD_TYPE_ID = '3', TRANS.TRANSACTION_AMOUNT, 0.00) AS APPLIED_AMOUNT,(SELECT CASH_ENTRY.DUE_DATE FROM CAS_CASH_ENTRY CASH_ENTRY WHERE CASH_ENTRY.CASH_ENTRY_ID = TRANS.RECORD_ID AND TRANS.RECORD_TYPE_ID = '3') AS DUE_DATE,(SELECT BATCH.PAYMENT_ID FROM CAS_CASH_DEPOSIT BATCH WHERE BATCH.DEPOSIT_ID = TRANS.BATCH_ID) AS CHECK_NUMBER,IF(TRANS.ALLOCATION_TYPE_ID = '13', TRANS.TRANSACTION_AMOUNT, 0.00) AS ACCRUAL_ADJUSTMENT,IF(TRANS.ALLOCATION_TYPE_ID = '10', TRANS.TRANSACTION_AMOUNT, 0.00) AS WRITE_OFF_AMOUNT,(SELECT SUM(TT.TRANSACTION_AMOUNT) FROM CAS_TRANSACTION TT WHERE TT.ALLOCATION_TYPE_ID IN ('9', '10', '13') AND (TT.TRANSACTION_DATE < TRANS.TRANSACTION_DATE OR TT.TRANSACTION_DATE = TRANS.TRANSACTION_DATE) AND TT.ID <= TRANS.ID AND TT.POLICY_ID = TRANS.POLICY_ID AND TT.ACCOUNTING_DATE <= CAST((NOW() - INTERVAL 5 HOUR) AS DATE)) AS BALANCE,(SELECT BATCH.DEPOSIT_ID FROM CAS_CASH_DEPOSIT BATCH WHERE BATCH.DEPOSIT_ID = TRANS.BATCH_ID) AS BATCH_ID,CONCAT(TRANS.POLICY_ID,DATE_FORMAT(TRANS.TRANSACTION_DATE,'%M%D%Y'),RIGHT(CONCAT('0000000000', TRANS.ID),11)) AS UNID,(SELECT CONCAT(SUBMITTERS.FIRST_NAME,' ',SUBMITTERS.LAST_NAME) FROM CAS_WORKDAY_EMPLOYEE SUBMITTERS, CAS_CASH_DEPOSIT DEP WHERE DEP.DEPOSIT_ID = TRANS.BATCH_ID AND SUBMITTERS.AON_NET_ID IS NOT NULL AND DEP.PROCESSOR = SUBMITTERS.AON_NET_ID AND (TRANS.ALLOCATION_TYPE_ID = '10' OR TRANS.ALLOCATION_TYPE_ID = '13')) AS SUBMITTER,(SELECT CONCAT(APPROVERS.FIRST_NAME,' ',APPROVERS.LAST_NAME) FROM CAS_WORKDAY_EMPLOYEE APPROVERS, CAS_AUDIT_HISTORY APPROVE_AUDIT, CAS_CASH_DEPOSIT BATCH WHERE BATCH.DEPOSIT_ID = TRANS.BATCH_ID AND APPROVERS.AON_NET_ID IS NOT NULL AND APPROVE_AUDIT.INITIATOR = APPROVERS.AON_NET_ID AND APPROVE_AUDIT.RECORD_ID = BATCH.DEPOSIT_ID AND APPROVE_AUDIT.RECORD_TYPE = '2' AND APPROVE_AUDIT.ACTION_TYPE_ID = '2563') AS APPROVERFROM CAS_TRANSACTION TRANS, CAS_POLICY POLICYWHERE TRANS.POLICY_ID = POLICY.POLICY_IDAND(TRANS.ALLOCATION_TYPE_ID = '9' OR TRANS.ALLOCATION_TYPE_ID = '10' OR TRANS.ALLOCATION_TYPE_ID = '13')AND POLICY.IS_ACCRUAL = '1'AND TRANS.ACCOUNTING_DATE <= CAST((NOW() - INTERVAL 5 HOUR) AS DATE)
This is currently causing timeout issues due to volume of data so we decided to create a physical table to be populated automatically via process model with same data from above then use that table in the View thus below would be the current SQL of the view
SELECT `ID` AS `ID`, `COMPANY` AS `COMPANY`, `CLIENT_ID` AS `CLIENT_ID`, `CLIENT` AS `CLIENT`, `PROJECT_ID` AS `PROJECT_ID`, `PROJECT` AS `PROJECT`, `ACTIVITY_ID` AS `ACTIVITY_ID`, `ACTIVITY` AS `ACTIVITY`, `COST_CENTER` AS `COST_CENTER`, `POLICY_ID` AS `POLICY_ID`, `CARRIER_POLICY` AS `CARRIER_POLICY`, `PRACTICE` AS `PRACTICE`, `CARRIER` AS `CARRIER`, `COVERAGE_CODE` AS `COVERAGE_CODE`, `POLICY_EFFECTIVE_DATE` AS `POLICY_EFFECTIVE_DATE`, `EXPIRATION_DATE` AS `EXPIRATION_DATE`, `TRANS_DATE` AS `TRANS_DATE`, `ACCOUNTING_DATE` AS `ACCOUNTING_DATE`, `DUE_DATE` AS `DUE_DATE`, `ACCRUAL_AMOUNT` AS `ACCRUAL_AMOUNT`, `CASH_AMOUNT` AS `CASH_AMOUNT`, `APPLIED_AMOUNT` AS `APPLIED_AMOUNT`, `CHECK_NUMBER` AS `CHECK_NUMBER`, `ACCRUAL_ADJUSTMENT` AS `ACCRUAL_ADJUSTMENT`, `WRITE_OFF_AMOUNT` AS `WRITE_OFF_AMOUNT`, `BALANCE` AS `BALANCE`, `BATCH_ID` AS `BATCH_ID`, `UNID` AS `UNID`, `SUBMITTER` AS `SUBMITTER`, `APPROVER` AS `APPROVER`FROM `CAS_ACCRUAL_REPORT_TBL`
But we are encountering below error when running the report via interface
Expression evaluation error [evaluation ID = IM9ZHLYW] in rule 'cas_reportshomedynamic' at function a!buttonWidget [line 568]: An error occurred while executing a save: Expression evaluation error in rule 'cas_getreportbyinputs' at function a!queryEntity [line 9]: An error occurred while retrieving the data.
When you try to query the view via database no issues are encountered thus we hope someone could provide assistance on this. Thanks!
Discussion posts and replies are publicly visible
Usually the first thing that error message might mean is that the query timed out within Appian. The system logs should be able to provide you slightly more detail on the error.
Hi Victor,
Could you check if you have any date field which returns "0000-00-00" if yes, could you update those entries and try again
I was able to find 0000-00-00 values on the dates and after updating them issue was fixed. Thanks a lot!!