I am trying to execute a query from past two days and I am stuck. Below is the query , let me know how I can run this :
UPDATE DBCOE_REPORT_QUERY
SET STATIC_QUERY = 'select pr.FAX_REF_ID ,pr.TXN_GRP_ID,pr.FAX_TYPE,TO_CHAR(inc.MAIL_SUBJECT) as EMAIL_SUBJECT,pr.SENDER,pr.REGION_CD,pr.SUB_BRANCH_CD,pr.BUSINESS_STATUS,TO_CHAR(pr.FAXRECEIVEDDATE,'DD_MON_YY HH24:MI:SS') as FAXRECEIVEDDATE,pr.PRIORITY,NVL(g.FAX_INDEMNITY_FLG,'NA') AS FAX_INDEMNITY_FLG,pr.NO_OF_TXN,g.CALLBACK_VER_REQ,g.ADTNL_MNGR_REQ,t.AMOUNT,t.QUANTITY, t.CURRENCY,t.DEBIT_ACCOUNT, t.CREDIT_ACCOUNT,t.VALUEDATESTR as VALUE_DATE,t.ISIN_SECID_NAME, t.ACC_CLNT_NAME,t.SETTLE_DATE,t.PRODUCT,t.PURPOSE, t.PAY_DATE,t.SETUP_CD,t.SENDER_REFERENCE,t.EXCHANGE_RATE,t.BENEFICIARY_BANK_ACCOUNT,t.BENEFICIARY_BANK,t.BENFCRY_NM_ADDRS,t.ADDNTL_INFO,t.NATURE_OF_CHARGES,t.PAYMENT_TYPE,t.SETTLEMENT_PLACE,t.SAFEKEEPING_PLACE, t.BENEFICIARY_AC_NO,t.CORP_ACTION_EVENT, rt.PREFAX1, rt.PREFAX1_DATE_TIME, rt.PREFAX2, rt.PREFAX2_DATE_TIME, rt.FAX_MANAGER, rt.FAX_MANAGER_DATE_TIME, rt.SIGNATURE_VERIFIER, rt.SV_DATE_TIME, rt.SIGNATURE_VERIFIER2, rt.SV2_DATE_TIME, rt.DETESTER,rt.DETESTER_DATE_TIME, rt.CALLBACK_VERIFIER, rt.CBV_DATE_TIME, rt.ADDITIONAL_MANAGER, rt.ADM_DATE_TIME, rt.PROCESSOR, rt.PROCESSOR_DATE_TIME, rt.RESEARCHER,rt.RESEARCHER_DATE_TIME, rt.APPROVER, rt.APPROVER_DATE_TIME, rt.AUTHORIZER,rt.AUTHORIZER_DATE_TIME, rt.OPS_MANAGER, rt.OPS_DATE_TIME,rt.FUNDS_FLOW_MANAGER,rt.FFM_DATE_TIME,cd.CALL_TO_NAME,cd.CALL_TEL_NUMBER_TO,cd.CALL_TEL_NUMBER_FROM,TO_CHAR(cd.CALL_DATE,'DD_MON_YY HH24:MI:SS') as CALL_DATE_TIME,i2.CLAIMED_BY,TO_CHAR(NVl(SUBSTR(gc.GENERAL_COMMENTS,1,4000),'NA')) as GENERAL_COMMENTS1,TO_CHAR(NVL(SUBSTR(gc.GENERAL_COMMENTS,4001,8000),'NA')) as GENERAL_COMMENTS2,TO_CHAR(NVL(SUBSTR(gc.GENERAL_COMMENTS,8001,12000),'NA')) as GENERAL_COMMENTS3,TO_CHAR(NVL(SUBSTR(gc.GENERAL_COMMENTS,12001,16000),'NA')) as GENERAL_COMMENTS4,TO_CHAR(NVL(SUBSTR(gc.GENERAL_COMMENTS,16001,20000),'NA')) as GENERAL_COMMENTS5 from HRM3_PR_META_INFO pr,HRM3_INCOMING_EMAILS inc,HRM3_FAX_DETAILS f,HRM3_TXNGRP_DETAILS g,VIEW_HRM3_TXN_DTLS t, VIEW_TASK_ROLE_TIME rt, VIEW_GENERAL_COMMENTS gc,HRM3_CALLBACK_DTLS cd,(select distinct HRM_TXN_GRP_ID, CLAIMED_BY from DBBPM_INBOX_METADATA_EXEC WHERE CLAIMED_BY<>'NA' and EXECUTIONSTATE='STARTED' and UOWTYPE='TXNGRP' )i2 WHERE pr.HRM_INCMNG_EMAIL_ID=inc.INCOMING_EMAIL_ID and pr.FAX_REF_ID=f.FAX_REFERENCE_ID and pr.TXN_GRP_ID=g.TXN_GRP_ID and pr.TXN_GRP_ID=rt.TXN_GRP_ID(+) AND pr.TXN_GRP_ID=cd.UOWID (+) and pr.TXN_GRP_ID=i2.HRM_TXN_GRP_ID(+) and pr.TXN_GRP_ID=gc.HRM_TXN_GRP_ID(+) and pr.UOWTYPE='TXNGRP'
WHERE REPORT_ID=600;
Issue I am facing here is as I am trying to update the column with another select query that contains multiple date functions and NVL function, so it is getting confused where the query is ending due to single inverted commas. If I run the select query standalone it is working fine.
How to check this : Paste this query as is in SQL file, you will see the error. Let me know a way to insert this value. Thanks in advance.
