How to update the below statement, i am getting error while update

January 10, 2023

I am trying to update the RRP_VALIDATION value as below, but getting error as sql command not ended properly, how to run the below statement with correct format.

UPDATE HES_REPORT_REF_PARAMS 
SET RRP_VALIDATION = 'select to_char(RECONCILED_ID), decode(STATUS, 'R', 'RECONCILED', 'UNRECONCILED')||' - bank '||BANK_CODE||' - date '||to_char(RECONCILED_DATE, 'DD-MON-YYYY') from HES_BANK_RECONCILED_GROUPS where CONTROL_CONTEXT_ID = TO_NUMBER(:PARAMETER.BPX_CCI) order by RECONCILED_ID desc'
WHERE HES_REPORT_REF_PARAMS.RRP_MODULE_NUM = '100';

>Solution :

Could you try replacing the single quotes with double single quotes like this:

UPDATE HES_REPORT_REF_PARAMS 
SET RRP_VALIDATION = 'select to_char(RECONCILED_ID), decode(STATUS, ''R'', ''RECONCILED'', ''UNRECONCILED'')||'' - bank ''||BANK_CODE||'' - date ''||to_char(RECONCILED_DATE, ''DD-MON-YYYY'') from HES_BANK_RECONCILED_GROUPS where CONTROL_CONTEXT_ID = TO_NUMBER(:PARAMETER.BPX_CCI) order by RECONCILED_ID desc'
WHERE HES_REPORT_REF_PARAMS.RRP_MODULE_NUM = '100';