topic Re: [resolved] globalMap.get in SQL Builder tas400input in Talend Studio

[resolved] globalMap.get in SQL Builder tas400input

cwaras — Tue, 28 Aug 2012 05:22:35 GMT

Hi
1) In a tSQLServerinput i select one line with a year value (ex : year_value=2011)
2) In a tAS400input i would like use this year value to filter directly in the where clause
Something like
select
...
from...
where
...
and x=year_value
I try to do that with globalMap.get but it doesn't work
Is globalMap.get is usefull in a tas400input ?
How can i do that by an other way ?
Great thx

Re: [resolved] globalMap.get in SQL Builder tas400input

Anonymous — Tue, 28 Aug 2012 08:19:05 GMT

Should work like this:
"select
...
from...
where
...
and x= +"globalMap.get("year_value")"+
and y = something
"
What is the exact error you are receving?

Re: [resolved] globalMap.get in SQL Builder tas400input

cwaras — Wed, 29 Aug 2012 00:40:30 GMT

Hi Saukema
And a great thank for answer.
I hope you will can help me.
I will try to describe better my problem :
I work on TOS 5.0.3.
You can find the global job picture below.
1) i select one line with three columns in a tSQLSErverInput :
SELECT KWHODS.ODS_DIM_TEMPS.ID_TEMPS as Temps,
cast(cast(KWHODS.ODS_DIM_TEMPS.ID_ANNEE as int)*10 as varchar(5)) as Annee,
cast(cast(KWHODS.ODS_DIM_TEMPS.CD_MOIS as int) as varchar(2)) as Mois
FROM KWHODS.ODS_DIM_TEMPS
"Temps", "Annee" and "Mois" are all varchar type.
2) i link that (main link) with a tSetGlobalVar, you can see the setting in the picture below
3) I lnik my tSetGlobalVar with my tAS400Input (on component ok link) and i put this in the SQL Builder of the t1SS400Input:

select x, y, z ....
from A a
where ....
and cast(a.REX as varchar(5))=" + (string)globalMap.get("Annee") + "
and cast(a.RPERIO as varchar(2))=" + (string)globalMap.get("Mois") + "
--> When i execute i receive the error :
Exception in component tAS400Input_1
java.sql.SQLException: Elément syntaxique ANNEE n'est pas correct. Eléments possibles : FOR SKIP WITH FETCH ORDER UNION EXCEPT OPTIMIZE.
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:650)
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:621)
at com.ibm.as400.access.AS400JDBCStatement.commonPrepare(AS400JDBCStatement.java:1578)
at com.ibm.as400.access.AS400JDBCStatement.executeQuery(AS400JDBCStatement.java:2136)
at kwh.copy_of_copy_of_load_fai_axfile_fca200j2_ods_b_1_0.Copy_of_Copy_of_Load_Fai_Axfile_FCA200J2_ODS_b.tAS400Input_1Process(Copy_of_Copy_of_Load_Fai_Axfile_FCA200J2_ODS_b.java:2496)
at kwh.copy_of_copy_of_load_fai_axfile_fca200j2_ods_b_1_0.Copy_of_Copy_of_Load_Fai_Axfile_FCA200J2_ODS_b.tMSSqlInput_1Process(Copy_of_Copy_of_Load_Fai_Axfile_FCA200J2_ODS_b.java:788)
at kwh.copy_of_copy_of_load_fai_axfile_fca200j2_ods_b_1_0.Copy_of_Copy_of_Load_Fai_Axfile_FCA200J2_ODS_b.runJobInTOS(Copy_of_Copy_of_Load_Fai_Axfile_FCA200J2_ODS_b.java:7519)

Without the 2 lines with the globalMap.get the request work without error....
What's wrong ????
Great great thx for yor help....

Re: [resolved] globalMap.get in SQL Builder tas400input

Anonymous — Wed, 29 Aug 2012 07:54:41 GMT

It looks pretty much ok to me, just a few things I can think of

Did you add "" to the complete statement?
so

"
select x, y, z ....
from A a
where ....
and cast(a.REX as varchar(5))=" + (string)globalMap.get("Annee") + "
and cast(a.RPERIO as varchar(2))=" + (string)globalMap.get("Mois") + "
"

Another suggestion I have is to add the query to the db connection in the repository. I've noticed Talend handles quotes a bit different in some situations with the repository.
Do you have a lot of data in ANAEL table? ohterwise you might consider to solve it with a join in the tMap instead of setting global vars.

Re: [resolved] globalMap.get in SQL Builder tas400input

cwaras — Thu, 30 Aug 2012 02:11:50 GMT

Well I finally found :
In the SQL Builder pop-up of the tAS400input, the code was good:
a.REX = "+ globalMap.get (" Year ") +"
But look more closely, in the thumbnail view of the query in the main window of the input tAS400 it became this:
a.REX = "/ + globalMap.get (" / Year / ") + /"
By removing the "/" it works ....
Bug or????
Thank you for your help anyway.