[resolved] globalMap.get in SQL Builder tas400input

Six Stars

[resolved] globalMap.get in SQL Builder tas400input

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

Accepted Solutions
Six Stars

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

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.

All Replies
One Star

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

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

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

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....
One Star

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

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.
Six Stars

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

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.

15TH OCTOBER, COUNTY HALL, LONDON

Join us at the Community Lounge.

Register Now

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Downloads and Trials

Test drive Talend's enterprise products.

Downloads