[resolved] Invalid escape sequence when defining SQL in (or out) of metadata
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.
Re: [resolved] Invalid escape sequence when defining SQL in (or out) of metadata
Hi, I'm having a \ inside my data and cannot be deleted. Is there any way to escape that character? right now my data is like "aaaaaa\b". so when it is written into the DB, it becomes "aaaaaab". Any solutions??? Thank You