topic Error when passing global map parameter in sql query in Talend Studio

Error when passing global map parameter in sql query

Anonymous — Sat, 16 Nov 2024 07:43:42 GMT

I have created a job like

DBInput---> row1(main)--->tFlowIterate-->DBinput

I wanted to use the result of first query as input parameter to the second query.

I posted the schema of first schema column and the second schema query is like

SELECT * FROM myTable where COUNT(1) = " + ((Integer)globalMap.get("row1.count_group")) + ");

Getting error as: Error near row1

Can anyone help me out what am I missing?

Thanks in advance,

Meghana.

Re: Error when passing global map parameter in sql query

fdenis — Mon, 03 Sep 2018 12:43:23 GMT

"SELECT * FROM myTable where COUNT(1) = " + (String)((Integer)globalMap.get("row1.count_group")) + ")"
with " and without ;

Re: Error when passing global map parameter in sql query

TRF — Mon, 03 Sep 2018 12:47:37 GMT

Should be like this:
"SELECT * FROM myTable where COUNT(1) = " + ((Integer)globalMap.get("row1.count_group"));

Double quote at the beginning, not ) at the end

Re: Error when passing global map parameter in sql query

Anonymous — Mon, 03 Sep 2018 14:01:45 GMT

It's not working, still I am getting same error.

select * from "employee_detail" where age = '"+((String)globalMap.get("row1.system_user_id"))+"'

The above query I used for another job and it worked fine.

But, I am getting error for this job as I mentioned before.

Thanks,

Meghana.

Re: Error when passing global map parameter in sql query

Anonymous — Mon, 03 Sep 2018 14:04:01 GMT

I am still getting error

Re: Error when passing global map parameter in sql query

fdenis — Mon, 03 Sep 2018 14:17:23 GMT

can you show us your error message.

Re: Error when passing global map parameter in sql query

Anonymous — Mon, 03 Sep 2018 14:19:29 GMT

Here it is

Screen Shot 2018-09-03 at 6.48.54 PM.png

Re: Error when passing global map parameter in sql query

fdenis — Mon, 03 Sep 2018 14:24:50 GMT

and your component configuration!

Re: Error when passing global map parameter in sql query

Anonymous — Mon, 03 Sep 2018 14:30:40 GMT

Configuration of which component first db or second db

Re: Error when passing global map parameter in sql query

fdenis — Mon, 03 Sep 2018 14:45:07 GMT

the one who is red or booth!

Re: Error when passing global map parameter in sql query

Anonymous — Mon, 03 Sep 2018 18:43:49 GMT

The problem I could see is the datatype of column in table is numeric

And when I use globalMap.get(row1.count_group) is not fetching.

I tried writing select * from myTable where columName = CAST(globalMap.get(row1.count_group) AS numeric);

But, the error is not fixed.

Is that the problem due to numeric type, because varchar(string) column types are working fine

Thanks,

Meghana

Re: Error when passing global map parameter in sql query

Anonymous — Tue, 04 Sep 2018 08:28:37 GMT

How do I write query..

Select * from where myColumn = '" + globalMap.get("something") + "';

Here myColumn is of numeric type.

I hope, If we knew how to write might solve my error.

when I do select '" + globalMap.get("something") + "' from dual, it is showing string '" + globalMap.get("something") + "'

Thanks,

Meghana

Re: Error when passing global map parameter in sql query

fdenis — Tue, 04 Sep 2018 08:37:28 GMT

globalMap.get(row1.count_group) is wrong
return return globalMap value named as the value of row1.count_group.

globalMap.get("row1.count_group") is good
return return globalMap value named row1.count_group.

just try inserting a number into the query.
then if it work replace the nuer by " + thenumber + "
then if it work replace the nuer by " + ((Intger)globalMap.get("row1.count_group")) + "

row1.count_group must be defined as Integer allow null in row1.
Regards

Re: Error when passing global map parameter in sql query

fdenis — Tue, 04 Sep 2018 09:29:22 GMT

problem is: no type are stored in globlMap
you have to define type(java) when you read data

are you still working on error on row1?

Re: Error when passing global map parameter in sql query

Anonymous — Tue, 04 Sep 2018 09:32:11 GMT

Yes still I am working on that.

No progress.

Actually globalMap.get is not reading data

Re: Error when passing global map parameter in sql query

fdenis — Tue, 04 Sep 2018 09:38:00 GMT

can you give screenshot of your components?

Re: Error when passing global map parameter in sql query

Anonymous — Tue, 04 Sep 2018 10:02:48 GMT

Here it is.

First of all globalMap is not reading data from row1

Screen Shot 2018-09-04 at 2.31.31 PM.png

Re: Error when passing global map parameter in sql query

fdenis — Tue, 04 Sep 2018 10:10:48 GMT

and can we also have components detail (querie config ….)?

Re: Error when passing global map parameter in sql query

Anonymous — Tue, 04 Sep 2018 10:15:36 GMT

I have attached query and row1 config

Screen Shot 2018-09-04 at 2.44.29 PM.png
Screen Shot 2018-09-04 at 2.44.04 PM.png
Screen Shot 2018-09-04 at 2.44.29 PM.png

Re: Error when passing global map parameter in sql query

fdenis — Tue, 04 Sep 2018 10:36:04 GMT

You cannot execute a query containing globalMap value in SQL Editor.
globalMap values are set during job execution.