topic Re: Unable to use the Global map ((String)globalMap.get("row1.column1")) in SQL query in Talend Studio

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

Anonymous — Sat, 16 Nov 2024 04:18:27 GMT

Hi Team,

I am not sure what I doing wrong but I am unable to use the ((String)globalMap.get("row1.column1")) variable in SQL query.

Please find the below design in TOS. Also, below is the query used after the tflowToIterate to process the each row from Input.

SELECT
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("row1. EXTRT_CNTRY_CD"))'

Please suggest.

Re: Unable to use the Global map ((String)globalMap.get("row1.column1")) in SQL query

manodwhb — Tue, 22 Oct 2019 10:34:09 GMT

@rohitatcs ,where you have row1 connection in your job screenshot?

Re: Unable to use the Global map ((String)globalMap.get("row1.column1")) in SQL query

TRF — Tue, 22 Oct 2019 10:35:31 GMT

Should be:

SELECT
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("row1. EXTRT_CNTRY_CD")) + "'"

Re: Unable to use the Global map ((String)globalMap.get("row1.column1")) in SQL query

Anonymous — Tue, 22 Oct 2019 10:44:29 GMT

Sorry I am new in this.So, I think it should be row7 instead row1 right as row7 is there on link.
Please confirm.

Re: Unable to use the Global map ((String)globalMap.get("row1.column1")) in SQL query

TRF — Tue, 22 Oct 2019 10:46:25 GMT

((String)globalMap.get("out1. EXTRT_CNTRY_CD"))
as out1 is the input flow for the tFlowToIterate component

Re: Unable to use the Global map ((String)globalMap.get("row1.column1")) in SQL query

manodwhb — Tue, 22 Oct 2019 11:03:01 GMT

SELECT
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= out1.EXTRT_CNTRY_CD

Re: Unable to use the Global map ((String)globalMap.get("row1.column1")) in SQL query

Anonymous — Tue, 22 Oct 2019 11:44:21 GMT

Thank you guys.

Here, is the solution for this.

SELECT
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"))

@TRF @manodwhb - can you guys suggest, how can we use these same global variable in Select query. We are now facing issue in this as compile error.

Re: Unable to use the Global map ((String)globalMap.get("row1.column1")) in SQL query

TRF — Tue, 22 Oct 2019 12:59:58 GMT

It seems that the variable "out1.EXTRT_YEAR" is declared as String not Integer

Re: Unable to use the Global map ((String)globalMap.get("row1.column1")) in SQL query

Anonymous — Tue, 22 Oct 2019 15:14:13 GMT

So can u suggest how to use same Global Variable in Select query.

Means, same in Where clause filter as well as in Select query.

Re: Unable to use the Global map ((String)globalMap.get("row1.column1")) in SQL query

TRF — Tue, 22 Oct 2019 15:16:54 GMT

Can you give some more details?

Re: Unable to use the Global map ((String)globalMap.get("row1.column1")) in SQL query

Anonymous — Wed, 23 Oct 2019 03:34:52 GMT

Sure, I want to use each row from input as table column in Select query .

Like we have already used global variable in the Where clause of query based on our input row iterate over similarly, at the same time I would like to add same global variable as additional column to table.

Example:

IF 1st row of input is like:-

Year Period

2019 9

Then in Postgresrow component, we will have something like that in Select query but

we are unable to use same Global variable in SELECT query which we used in WHERE clause.

SELECT

<Need to use here Global variable same as in Where clause like '" +((String)globalMap.get("out1.EXTRT_CNTRY_CD")) + ">,

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"))

Please let me know if you need more details, per my previous comment above, the error details are given while using Global variable in Select command.

Re: Unable to use the Global map ((String)globalMap.get("row1.column1")) in SQL query

Anonymous — Wed, 23 Oct 2019 09:10:18 GMT

Can someone please update on this.

Re: Unable to use the Global map ((String)globalMap.get("row1.column1")) in SQL query

Anonymous — Wed, 23 Oct 2019 09:40:03 GMT

Hi
It is a compilation error, can you show us the schema of output table on tMap1?

Regards
Shong

Re: Unable to use the Global map ((String)globalMap.get("row1.column1")) in SQL query

Anonymous — Wed, 23 Oct 2019 09:40:25 GMT

Could you please send here a screenshot (or the whole content) of your database input component query.

We actually do not know what went wrong here because all hints so far are correct.

Re: Unable to use the Global map ((String)globalMap.get("row1.column1")) in SQL query

Anonymous — Wed, 23 Oct 2019 12:54:45 GMT

Thanks for all inputs.

Finally this worked.

"SELECT " + ((String)globalMap.get("out1.EXTRT_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"))