topic Re: [resolved] Invalid escape sequence when defining SQL in (or out) of metadata in Talend Studio

[resolved] Invalid escape sequence when defining SQL in (or out) of metadata

Anonymous — Sat, 16 Nov 2024 13:51:02 GMT

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,

Re: [resolved] Invalid escape sequence when defining SQL in (or out) of metadata

Anonymous — Fri, 31 Jul 2009 03:24:48 GMT

Hello

'\i2b2\Diagnoses%') i

The problem come from '\i' and '\D', change it to '\\i' '\\D' or delete them.
Best regards
shong

Re: [resolved] Invalid escape sequence when defining SQL in (or out) of metadata

Anonymous — Fri, 31 Jul 2009 15:41:18 GMT

Yup, that worked. I should've recognized that was the problem. Thanks for your help.

Re: [resolved] Invalid escape sequence when defining SQL in (or out) of metadata

Anonymous — Tue, 15 Nov 2011 08:18:36 GMT

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

Re: [resolved] Invalid escape sequence when defining SQL in (or out) of metadata

Anonymous — Fri, 14 Sep 2012 10:25:40 GMT

I have a file that contains 1|Test and want to load on Hive external table. I use thiverow but getting error
Invalid escape sequence.
Please help me to sort this.