Updating an SQL query in a business reporter

Can’t be too sure as I’m not super familiar with Business Reporter but I’d suggest looking up the LAST_SQL_STATEMENT column in View DSOD_SET_EXEC_PARAM

I found the sql query which is having an issue but I don’t understand the origin of the issue. The error occurs in this sql query

SELECT 'a'      FROM ifsapp.NON_CONFORMANCE_REPORT_CFV      LEFT JOIN      (SELECT ifsapp.SHOP_ORDER_OPERATION_JOIN.WORK_CENTER_NO,ifsapp.NCR_OBJECT_CONNECTION_CFV.NCR_NO, ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME, ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF FROM ifsapp.NCR_OBJECT_CONNECTION_CFV           LEFT JOIN ifsapp.SHOP_ORDER_OPERATION_JOIN ON          (         ifsapp.SHOP_ORDER_OPERATION_JOIN.ORDER_NO = REPLACE(substr(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF), instr(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF), ':',1,2)+1, instr(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF), ',',1,2)-instr(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF), ':',1,2)-1),' ',NULL) AND         ifsapp.SHOP_ORDER_OPERATION_JOIN.OPERATION_NO = REPLACE(substr(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF), instr(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF), ':',1,1)+1, instr(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF), ',',1,1)-instr(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF), ':',1,1)-1),' ',NULL) AND         ifsapp.SHOP_ORDER_OPERATION_JOIN.RELEASE_NO = REPLACE(substr(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF), instr(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF), ':',1,3)+1, instr(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF), ',',1,3)-instr(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF), ':',1,3)-1),' ',NULL) AND         ifsapp.SHOP_ORDER_OPERATION_JOIN.SEQUENCE_NO = REPLACE(substr(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF), instr(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF), ':',1,4)+1, length(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF))-instr(ifsapp.OBJECT_CONNECTION_SYS.Get_Instance_Description(ifsapp.NCR_OBJECT_CONNECTION_CFV.LU_NAME,NULL,ifsapp.NCR_OBJECT_CONNECTION_CFV.KEY_REF), ':',1,4)),' ',NULL)         ) WHERE CF$_SFD_CF_QM_013_DB = 'OCCURENCE') T1          ON (T1.NCR_NO = ifsapp.NON_CONFORMANCE_REPORT_CFV.NCR_NO     )          LEFT JOIN      (SELECT NCR_NO,LU_NAME,KEY_REF FROM ifsapp.NCR_OBJECT_CONNECTION_CFV  WHERE CF$_SFD_CF_QM_013_DB = 'DETECTION') T2          ON (T2.NCR_NO = ifsapp.NON_CONFORMANCE_REPORT_CFV.NCR_NO)     LEFT JOIN         (SELECT T3.NCR_NO, T3.MRB_NUMBER, ifsapp.INVENTORY_TRANSACTION_HIST2.LOT_BATCH_NO, ifsapp.INVENTORY_TRANSACTION_HIST2.DATE_CREATED FROM ifsapp.INVENTORY_TRANSACTION_HIST2 INNER JOIN             (SELECT DISTINCT ifsapp.NCR_OBJECT_CONNECTION_CFV.NCR_NO, ifsapp.MRB_PART_DETAIL.MRB_NUMBER, ifsapp.MRB_PART_DETAIL.LOT_BATCH_NO FROM ifsapp.MRB_PART_DETAIL INNER JOIN ifsapp.NCR_OBJECT_CONNECTION_CFV                  ON (to_char(ifsapp.MRB_PART_DETAIL.MRB_NUMBER) = ifsapp.CLIENT_SYS.Get_Key_Reference_Value(NCR_OBJECT_CONNECTION_CFV.KEY_REF,'MRB_NUMBER')                  AND ifsapp.NCR_OBJECT_CONNECTION_CFV.Lu_NAME = 'MrbHead' AND ifsapp.MRB_PART_DETAIL.LOT_BATCH_NO <> '*' AND ifsapp.NCR_OBJECT_CONNECTION_CFV.NCR_NO is not null)) T3             ON ifsapp.INVENTORY_TRANSACTION_HIST2.LOT_BATCH_NO = T3.LOT_BATCH_NO AND ifsapp.INVENTORY_TRANSACTION_HIST2.TRANSACTION_CODE = 'OOREC') T4          ON to_char(T4.NCR_NO) = to_char(ifsapp.NON_CONFORMANCE_REPORT_CFV.NCR_NO)     where NON_CONFORMANCE_REPORT_CFV.DATE_CREATED>=to_date('01/06/2025', 'dd/MM/yyyy')
 

When I remove the last join, the query works, so the issue occurs on here :

    LEFT JOIN         (SELECT T3.NCR_NO, T3.MRB_NUMBER, ifsapp.INVENTORY_TRANSACTION_HIST2.LOT_BATCH_NO, ifsapp.INVENTORY_TRANSACTION_HIST2.DATE_CREATED FROM ifsapp.INVENTORY_TRANSACTION_HIST2 INNER JOIN             (SELECT DISTINCT ifsapp.NCR_OBJECT_CONNECTION_CFV.NCR_NO, ifsapp.MRB_PART_DETAIL.MRB_NUMBER, ifsapp.MRB_PART_DETAIL.LOT_BATCH_NO FROM ifsapp.MRB_PART_DETAIL INNER JOIN ifsapp.NCR_OBJECT_CONNECTION_CFV                  ON (to_char(ifsapp.MRB_PART_DETAIL.MRB_NUMBER) = ifsapp.CLIENT_SYS.Get_Key_Reference_Value(NCR_OBJECT_CONNECTION_CFV.KEY_REF,'MRB_NUMBER')                  AND ifsapp.NCR_OBJECT_CONNECTION_CFV.Lu_NAME = 'MrbHead' AND ifsapp.MRB_PART_DETAIL.LOT_BATCH_NO <> '*' AND ifsapp.NCR_OBJECT_CONNECTION_CFV.NCR_NO is not null)) T3             ON ifsapp.INVENTORY_TRANSACTION_HIST2.LOT_BATCH_NO = T3.LOT_BATCH_NO AND ifsapp.INVENTORY_TRANSACTION_HIST2.TRANSACTION_CODE = 'OOREC') T4          ON to_char(T4.NCR_NO) = to_char(ifsapp.NON_CONFORMANCE_REPORT_CFV.NCR_NO)

but strangely, I can run this query with no issue. The error is :

"unable to convert string value containing %s to a number: %s"