Unable to use the Global map ((String)globalMap.get("row1.column1")) in SELECT Query

[Anonymous]

Hi Team,

We are unable to use Global variable in Select query.

"SELECT" + ((Integer)globalMap.get("out1.EXTRT_YEAR")) +" ,VMRCTTA1.VMRRCNTRY_WORKNG_DAYS.CNTRY_CD,
VMRCTTA1.VMRRCNTRY_WORKNG_DAYS.CNTRY_YEAR,
VMRCTTA1.VMRRCNTRY_WORKNG_DAYS.CNTRY_PERIOD,
VMRCTTA1.VMRRCNTRY_WORKNG_DAYS.CNTRY_WORKNG_DAYS,
VMRCTTA1.VMRRCNTRY_WORKNG_DAYS.CNTRY_UPDTD_BY,
VMRCTTA1.VMRRCNTRY_WORKNG_DAYS.CNTRY_UPDTD_DT,
VMRCTTA1.VMRRCNTRY_WORKNG_DAYS.CNTRY_UPDTD_BY_ROLE
FROM VMRCTTA1.VMRRCNTRY_WORKNG_DAYS
where CNTRY_CD='" +((String)globalMap.get("out1.EXTRT_CNTRY_CD")) + "'
and CNTRY_YEAR= " + ((Integer)globalMap.get("out1.EXTRT_YEAR")

IN where clause it works fine.Please suggest.

Here is the error on execution.

[statistics] connected
Exception in component tDBInput_2 (DUmmy_test)
com.ibm.db2.jcc.am.SqlException: [jcc][10103][10941][3.66.46] Method executeQuery cannot be used for update. ERRORCODE=-4476, SQLSTATE=null

[TRF]

What if you try the same query (with the same value) from outside TOS? Is there a result or no rows?

[Anonymous]

Yes, it gives output. It's just hardcode value and coming as same year used in Where clause.

So, query look like as below.

SELECT 2019 ,VMRCTTA1.VMRRCNTRY_WORKNG_DAYS.CNTRY_CD,
VMRCTTA1.VMRRCNTRY_WORKNG_DAYS.CNTRY_YEAR,
VMRCTTA1.VMRRCNTRY_WORKNG_DAYS.CNTRY_PERIOD,
VMRCTTA1.VMRRCNTRY_WORKNG_DAYS.CNTRY_WORKNG_DAYS,
VMRCTTA1.VMRRCNTRY_WORKNG_DAYS.CNTRY_UPDTD_BY,
VMRCTTA1.VMRRCNTRY_WORKNG_DAYS.CNTRY_UPDTD_DT,
VMRCTTA1.VMRRCNTRY_WORKNG_DAYS.CNTRY_UPDTD_BY_ROLE
FROM VMRCTTA1.VMRRCNTRY_WORKNG_DAYS
where CNTRY_CD='531'
and CNTRY_YEAR= 2019;

[Anonymous]

Hi,

     Even though its an integer in the SQL statement, when you are formulating the statement, the data for the year should be concatenated as a string with the rest of the SQL statement. So the best idea will be to do a quick conversion of data to a String Global variable and then do the concatenation.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved