I am trying to port some of my plsql ETL into the repository (into the metadata as a query). I have done this many times and feel comfortable with the process. For some reason, however, I keep getting the following error message for a particular SQL query:
Invalid escape sequence (valid ones are \b \t \n \f \r \" \' \\ )
The query is a bit bulky, but I thought I'd include it anyway.
insert /*+ append */ into i2b2demodata.observation_fact( ENCOUNTER_NUM,
PATIENT_NUM, CONCEPT_CD, PROVIDER_ID,
START_DATE, MODIFIER_CD, VALTYPE_CD,
TVAL_CHAR, NVAL_NUM, VALUEFLAG_CD,
QUANTITY_NUM, UNITS_CD, END_DATE,
LOCATION_CD, CONFIDENCE_NUM,
OBSERVATION_BLOB, UPDATE_DATE,
DOWNLOAD_DATE, IMPORT_DATE,
SOURCESYSTEM_CD,
UPLOAD_ID ) with DIAG_DX as ( select ids.enc_num
ENCOUNTER_NUM, crm.me_mpi PATIENT_NUM,
i.c_basecode concept_cd,
ids.PROVIDER_ID PROVIDER_ID,
ids.start_date START_DATE,
ids.modifier_cd MODIFIER_CD,
NULL VALTYPE_CD,
NULL TVAL_CHAR,
NULL NVAL_NUM,
NULL VALUEFLAG_CD,
NULL QUANTITY_NUM, NULL UNITS_CD,
ids.end_date END_DATE,
ids.location_cd LOCATION_CD,
NULL CONFIDENCE_NUM,
NULL OBSERVATION_BLOB,
SYSDATE UPDATE_DATE,
SYSDATE DOWNLOAD_DATE,
SYSDATE IMPORT_DATE,
ids.sourcesystem_cd SOURCESYSTEM_CD,
NULL UPLOAD_ID from ICD9_DX_STAGE ids
INNER JOIN (select distinct i.c_basecode from i2b2metadata.i2b2 i where
i.c_fullname LIKE '\i2b2\Diagnoses%') i on (ids.concept_cd =
regexp_replace(i.c_basecode,'ICD9:',
'')) INNER JOIN CNSLDTN_RDW_MPI_ETL crm ON (crm.src_mrn =
ids.pat_mrn_id) ) select * from DIAG_DX"
My first thoughts were that the Oracle hint (/*+ append */) was causing it problems, but it turns out that isn't the issue (I was able to compile with a short query and the hint). I can't determine where the SQL is contains an invalid sequence.
This is generating a java error and never gets to the Oracle side, so we don't even have to discuss the DBMS right now.
Please help!
Thanks,
