Hi Everyone.
I am getting a ORA-00905: MISSING KEYWORD error before the insert statement when i am trying to run the following query
MERGE INTO star_dispatch_status a
USING
(select
null dispatch_status_id ,
trim(REGEXP_SUBSTR(TMP.CONTRACT_NO,'[^/\, ]+')) dispatch_id,
CASE
WHEN trim(sts)='NH' THEN 120
WHEN trim(sts)='WP' THEN 100
WHEN trim(sts)='PA' THEN 20
WHEN trim(sts)='CA' THEN 40
WHEN trim(sts)='CO' THEN 10
WHEN trim(sts)='AT' THEN 70
WHEN trim(sts)='O' THEN 150
WHEN trim(sts)='RN' THEN 60
WHEN trim(sts)='WS' THEN 30
WHEN trim(sts)='ED' THEN 130
ELSE NULL
END status_code,
'DISPSTAT' status_prefix,
trim(ORIG_SCHDT) status_timestamp ,
'SEARSFILE' application_code ,
'APP_NAME' application_prefix ,
trim(SYSDATE) last_modified ,
'SEARSAUTOIMP' last_modified_by
from tmp_dispatch_status tmp
where sts not in ('CO','CA') and trim(contract_no)='297281132/94971') b
ON (trim(a.dispatch_id) = trim(b.dispatch_id))
WHEN MATCHED THEN
INSERT (a.dispatch_id, a.status_code, a.status_prefix, a.status_timestamp,a.application_code,a.application_prefix,a.last_modified,a.last_modified_by)
VALUES (b.dispatch_id, b.status_code, 'DISPSTAT', b.status_timestamp,'SEARSFILE','APP_NAME',b.last_modified,'SEARSAUTOIMP')
/*UPDATE SET
a.dispatch_id=b.dispatch_id
a.status_code = b.status_code,
a.status_prefix = b.status_prefix,
--a. status_timestamp= b.status_timestamp,
a.application_code=b.application_code,
a.application_prefix=b.application_prefix,
a.last_modified=b.last_modified,
a.last_modified_by=b.last_modified_by)*/
WHEN NOT MATCHED THEN
NULL;
COMMIT;