using cast_ws in mysql qurey to include double quotes to a string

[Sudhee_Maximus]

Hi all there ,

I know it can be done using Talend function StringHandling.DQUOTE in tMap...

but I am trying to do something as below which throws error ... why is it not allowing the cast_ws function in Talned sql qurey ....

[Anonymous · Accepted solution]

The SQL code window in all of the DB components is treated as a Java String. This allows you to create dynamic SQL quite easily. However, it means that you need to abide by Java String manipulation rules. If you want to use double quotes, you will need to escape them with a the Java String escape character. So, as an example if I want to filter my query using the value "Hello" (where the double quotes are part of the value), I would have to do this.....

"Select
Col1,
Col2,
Col3,
Col4
From MyTable
Where Col1 = '\"Hello\"'"

The whole query is surrounded by double quotes, because it is a Java String. The double quotes that need to be read by Java as double quotes are escaped using \.

[Sudhee_Maximus]

can that be used with in a function of SQL like what I was trying to do  ? because even after using escape char with in the function (the job succeeds but not serve the purpose ).

using in where clause is a generic exampe but with function can this be solved or any other solution to use this with in a function like 

CASE WHEN P_LANG_CD1 = null or P_LANG_CD1 ='' THEN null else concat_ws(P_LANG_CD1,\",\") END P_LANG_CD1,
or 

CASE WHEN P_LANG_CD1 = null or P_LANG_CD1 ='' THEN null else CAST(concat_ws(P_LANG_CD1,\",\")AS CHAR) END P_LANG_CD1, 

I need help specifically to this example ... 

[Anonymous]

Can you post the whole query please? I have no idea what you are trying to do and it kind of matters. You need to be sure that both the SQL is correct and that the Java is correct. Also, what errors are you getting?

[Sudhee_Maximus]

---- I am using this as part of replacing values with double quotes when value is available else to pass null ... this requirement is specific to a use case where I am creating JSON using this Input (I can achieve this with variables using talend but feels this is the easy way to distribute work load to sql instead of Talend )

please find the query below  ....

SELECT
ROW_ID,
JOB_ID,
substr(P_CMO_CODE,1,9) as P_CMO_CODE,
P_CNTY_CD_LOC,
P_TY_CD,
P_GROUP,
P_ID,
P_LAST_NAM,
P_FST_NAM,
P_MI_NAM,
P_SFX_NAM,
P_TITL_NAM,
P_SSN_NUM,
P_FEIN_ID,
GL_LINE1_AD,
CASE WHEN GL_LINE2_AD = null or trim(GL_LINE2_AD) ='' THEN null else concat_ws(GL_LINE2_AD,'"','"') END GL_LINE2_AD,
GL_CITY_NAM,
GL_ST_CD,
GL_ZIP_CD,
GL_PHON_NUM,
GL_FAX_NUM,
GL_EMAIL_AD,
GM_LINE1_AD,
CASE WHEN GM_LINE2_AD = null or trim(GM_LINE2_AD) ='' THEN null else concat_ws(GM_LINE2_AD,'"','"') END GM_LINE2_AD,
GM_CITY_NAM,
GM_ST_CD,
GM_ZIP5_CD,
P_CNTY_CD_MAILING,
GM_PHON_NUM,
GM_FAX_NUM,
CASE WHEN P_LANG_CD1 = null or P_LANG_CD1 ='' THEN null else CAST(concat_ws(P_LANG_CD1,'"','"')AS CHAR) END P_LANG_CD1,
CASE WHEN P_LANG_CD2 = null or P_LANG_CD2 ='' THEN null else CAST(concat_ws(P_LANG_CD2,'"','"')AS CHAR) END P_LANG_CD2,
CASE WHEN P_LANG_CD3 = null or P_LANG_CD3 ='' THEN null else CAST(concat_ws(P_LANG_CD3,'"','"')AS CHAR) END P_LANG_CD3,
CASE WHEN P_LANG_CD4 = null or P_LANG_CD4 ='' THEN null else CAST(concat_ws(P_LANG_CD4,'"','"')AS CHAR) END P_LANG_CD4,
CASE WHEN P_LANG_CD5 = null or P_LANG_CD5 ='' THEN null else CAST(concat_ws(P_LANG_CD5,'"','"')AS CHAR) END P_LANG_CD5,
CASE WHEN P_LANG_CD6 = null or P_LANG_CD6 ='' THEN null else CAST(concat_ws(P_LANG_CD6,'"','"')AS CHAR) END P_LANG_CD6,
CASE WHEN P_LANG_CD7 = null or P_LANG_CD7 ='' THEN null else CAST(concat_ws(P_LANG_CD7,'"','"')AS CHAR) END P_LANG_CD7,
P_GENDER_CD,
P_SPECL_CTR,
CASE WHEN P_SPECL_CD1 = null or P_SPECL_CD1 ='' THEN null else CAST(concat_ws(P_SPECL_CD1,'"','"')AS CHAR) END P_SPECL_CD1,
P_SPECL_BEG_DT1,
P_SPECL_END_DT1,
CASE WHEN P_SPECL_CD2 = null or P_SPECL_CD2 ='' THEN null else CAST(concat_ws(P_SPECL_CD2,'"','"')AS CHAR) END P_SPECL_CD2,
P_SPECL_BEG_DT2,
P_SPECL_END_DT2,
CASE WHEN P_SPECL_CD3 = null or P_SPECL_CD3 ='' THEN null else CAST(concat_ws(P_SPECL_CD3,'"','"')AS CHAR) END P_SPECL_CD3,
P_SPECL_BEG_DT3,
P_SPECL_END_DT3,
CASE WHEN P_SPECL_CD4 = null or P_SPECL_CD4 ='' THEN null else CAST(concat_ws(P_SPECL_CD4,'"','"')AS CHAR) END P_SPECL_CD4,
P_SPECL_BEG_DT4,
P_SPECL_END_DT4,
CASE WHEN P_SPECL_CD5 = null or P_SPECL_CD5 ='' THEN null else CAST(concat_ws(P_SPECL_CD5,'"','"')AS CHAR) END P_SPECL_CD5,
P_SPECL_BEG_DT5,
P_SPECL_END_DT5,
CASE WHEN P_SPECL_CD6 = null or P_SPECL_CD6 ='' THEN null else CAST(concat_ws(P_SPECL_CD6,'"','"')AS CHAR) END P_SPECL_CD6,
P_SPECL_BEG_DT6,
P_SPECL_END_DT6,
CASE WHEN P_SPECL_CD7 = null or P_SPECL_CD7 ='' THEN null else CAST(concat_ws(P_SPECL_CD7,'"','"')AS CHAR) END P_SPECL_CD7,
P_SPECL_BEG_DT7,
P_SPECL_END_DT7,
CASE WHEN P_SPECL_CD8 = null or P_SPECL_CD8 ='' THEN null else CAST(concat_ws(P_SPECL_CD8,'"','"')AS CHAR) END P_SPECL_CD8,
P_SPECL_BEG_DT8,
P_SPECL_END_DT8,
CASE WHEN P_SPECL_CD9 = null or P_SPECL_CD9 ='' THEN null else cast(concat_ws(P_SPECL_CD9,'"','"')AS CHAR) END P_SPECL_CD9,
P_SPECL_BEG_DT9,
P_SPECL_END_DT9,
CASE WHEN P_SPECL_CD10 = null or P_SPECL_CD10 ='' THEN null else cast(concat_ws(P_SPECL_CD10,'"','"') as CHAR) END P_SPECL_CD10,
P_SPECL_BEG_DT10,
P_SPECL_END_DT10,
P_AGEMIN,
P_AGEMAX,
P_HANDICAP,
P_HOURS_MO,
P_OPENHR_MO,
P_CLOSEHR_MO,
P_HOURS_TU,
P_OPENHR_TU,
P_CLOSEHR_TU,
P_HOURS_WE,
P_OPENHR_WE,
P_CLOSEHR_WE,
P_HOURS_TH,
P_OPENHR_TH,
P_CLOSEHR_TH,
P_HOURS_FR,
P_OPENHR_FR,
P_CLOSEHR_FR,
P_HOURS_SA,
P_OPENHR_SA,
P_CLOSEHR_SA,
P_HOURS_SU,
P_OPENHR_SU,
P_CLOSEHR_SU,
NPI,
CRT_DT,
UPD_DT,
JOB_NAME,
ERROR_CODE,
ERROR_MSG
FROM ETL_NETWORK_INIT
WHERE JOB_ID=351 limit 2

[Anonymous]

The first question I have is, does this code work in a SQL query analyser? Can you get the results you are looking for back? If so, all you should need to do is escape the double quotes (\") and add a double quote before the SELECT and a double quote at the end. That should work. What error were you getting?

[Sudhee_Maximus]

yes I am able to get results in workbench , but even after the escape ( \")  it is not working for me in talend .
Error : same as the one I attached ....

[Sudhee_Maximus]

Hi Rhall,

I guess this is working for me now with the below changes to the query 

CASE WHEN P_LANG_CD1 = null or P_LANG_CD1 ='' THEN null else concat_ws(P_LANG_CD1,'\"','\"') END P_LANG_CD1,

if any issues will let you know , thanks for your time on this ...