How to get the values dynamically in tOracleInput query

[Anonymous]

I wanted to apply filter to source query with different filter condition at every run
so, I need to take the values in where condition using a file( through context variable)
Example:
I have written the below in
toracleInput
"Select * from table1" +context.filename
I have given the filter in the file
"where col3 in (val1,val2, val3)"
It is not taking the filter condition from file
If i try one other ways it is throwing error.
Please let me know the appropriate way to write dynamic queries in talend

[Anonymous]

Dear Cterenzi,
Thanks for constantly helping me.
It seems that problem is from my side. I couldn't explain my issue properly.
In this case,we are taking one excel sheet which is having list of TNs. In the list we have around 2000 Tns in average . ( each Telephone Numbers will have 10 letters)
everyday we will get these excels. we are taking all Tns and running them inside a query one by one and get the value based on different condition . this process is very time consuming.
So I first , consolidated all queries in one and get the output in five different columns. once we have all the data , we are sending result of different columns in different mails to different people.
Now as we have more then 2000 Tns in the list , whatever approach I take , I will get a big string.
firstly I tried to distribute the tns in different parts but don't know how to do it( tried Java, Java script inside talend). Also it will take 15-20 variables to store all tns.
second I tried to use clob data but failed again.
then I tried to put it in anonymous block with no success. Then we tried almost all palletes available , again failed.and so on.....
So finally there is only one problem, size of variable is not going to be reduced, then how to use it within the query.
If we manage to do so, it will make our life easy.

[cterenzi]

I didn't know the scope of your case.  Even if you construct the query the way I described, it sounds like you'll run into a limitation on the number of items in your IN clause. (The last time I used Oracle, this was 1000 values.)
If there's a way to partition the list of telephone numbers to create smaller queries, using an IN clause might work.  Otherwise, you'll either need to insert the numbers to a table and do a join or pull down the entire table you want to query and do a lookup within Talend.  If your table is very large, pulling it into Talend may not be feasible.

[Anonymous]

Hi Cterenzi,
You are right we can not process more then 1000 inputs , but if we are getting this data from other table we can .
Here if it is sending such error , we can restrict user from using more then 1000 tns. We can tell them to split file into two or three parts.
We can not tell them to use only 300 Tns at one time, This will rise questions on this job.

I am trying this option, ( this is just POC that's why I introduced tJavaRow in middle).
using only 401 Tns in excel file.
In TFlowToIterate,
I have assign :
Key "Tns"
value "Tns"
In tOracleInput , my query is
"select  ' " + globalMap.get("TNs1")) + " ' as outs from dual" ( Also tried different ways to get some output).
in case if i am trying in wrong direction , please alert me . else guide me how to use it ....

[Anonymous]

second screen shot for your refrence

[Anonymous]

Hi Friends,
After checking so many things , I can say that I learn a lot in Talend.
Now my Job looks something like that.

However I am getting null value for the same query.
So still more effort needed to fix this issue.
It seems Either I will be expert in Talend before finishing this job or won't touch it again in y life
----------------------------------------------------------------------------------------------------------------
Anyways thanks guys for helping me specially Cterenzi.
Its 3 AM here , so going to sleep . I will try to fix this tomorrow.
If I find any solution. I will post it here so others can get some idea.

[vapukov]

Hi Cterenzi,
You are right we can not process more then 1000 inputs , but if we are getting this data from other table we can .
Here if it is sending such error , we can restrict user from using more then 1000 tns. We can tell them to split file into two or three parts.
We can not tell them to use only 300 Tns at one time, This will rise questions on this job.

I am trying this option, ( this is just POC that's why I introduced tJavaRow in middle).
using only 401 Tns in excel file.
In TFlowToIterate,
I have assign :
Key "Tns"
value "Tns"
In tOracleInput , my query is
"select  ' " + globalMap.get("TNs1")) + " ' as outs from dual" ( Also tried different ways to get some output).
in case if i am trying in wrong direction , please alert me . else guide me how to use it ....

not sure, what are You try to do, but You do something wrong 
tOracleInput - MUST have output connection, it MUST send data somewhere (forward them), so - both of Yours screenshots - not finished. Until it not finish - it always will be error

[Anonymous]

Hi Cterenzi,
You are right we can not process more then 1000 inputs , but if we are getting this data from other table we can .
Here if it is sending such error , we can restrict user from using more then 1000 tns. We can tell them to split file into two or three parts.
We can not tell them to use only 300 Tns at one time, This will rise questions on this job.

I am trying this option, ( this is just POC that's why I introduced tJavaRow in middle).
using only 401 Tns in excel file.
In TFlowToIterate,
I have assign :
Key "Tns"
value "Tns"
In tOracleInput , my query is
"select  ' " + globalMap.get("TNs1")) + " ' as outs from dual" ( Also tried different ways to get some output).
in case if i am trying in wrong direction , please alert me . else guide me how to use it ....

You may have an issue with your usage of your globalMap variables. When you assign a globalMap variable using the tFlowToIterate, the key is a String (which you have as "Tns") and the value can be hard coded (which is what you appear to have done here with "Tns"), but I suspect you wanted to set it to be a row value. To do this you need to refer to the row and columns without quotes like this....
row1.Tns
....for a row (link between components) called row1 and a column called Tns.
GlobalMap variables are also case sensitive when you use them and MUST be called by the exact key they are set with. In your example this is not the case. In addition to this, you need to cast the type since globalMap variables are stored as Objects. So, assuming your globalMap has a key of "Tns" and you correct it to be set to the correct value and not a hard coded value, your SQL query should be something like below....
"select  ' " + ((String)globalMap.get("Tns")) + " ' as outs from dual"
Try adding a tLogRow to your DB components as well. Otherwise you won't see any output.
Hope this helps

[Anonymous]

Hi Vopukov,
Thanks for your input.
as you said " tOracleInput - MUST have output connection" I have added tLogrow for that.
( I guess I can't sleep until This issue comes to any conclusion )

[cterenzi]

In TFlowToIterate,
I have assign :
Key "Tns"
value "Tns"
In tOracleInput , my query is
"select  ' " + globalMap.get("TNs1")) + " ' as outs from dual" ( Also tried different ways to get some output).

I'm not sure about the global variable you listed.  If you look in the Outline section (bottom left corner) and expand tFlowToIterate, it will list the variables you can use.  You can drag the Tns variable into your query and Talend will insert the proper code for you.

[Anonymous]

Hi Cterenzi,
You are right we can not process more then 1000 inputs , but if we are getting this data from other table we can .
Here if it is sending such error , we can restrict user from using more then 1000 tns. We can tell them to split file into two or three parts.

row1.Tns
....for a row (link between components) called row1 and a column called Tns.
-----------------------------------------------------------------------------------------
GlobalMap variables are also case sensitive when you use them and MUST be called by the exact key they are set with. In your example this is not the case. In addition to this, you need to cast the type since globalMap variables are stored as Objects. So, assuming your globalMap has a key of "Tns" and you correct it to be set to the correct value and not a hard coded value, your SQL query should be something like below....
"select  ' " + ((String)globalMap.get("Tns")) + " ' as outs from dual"
Try adding a tLogRow to your DB components as well. Otherwise you won't see any output.
Hope this helps
Hi Rhall/Friends,
I guess you are right.
First of all I haven't created any global variable named Tns. I just pressed ctrl+space and pick it from the list.
Second I can see that it is passing 407 Tns to tOracleInput but output is null. ( This screen shot is a part of real Job which I am working right now ). I tried row.Tns/Row1.Tns etc but in that case it is giving some compilation errors at tOracleInput.
Is there any tutorial available online which can help me to get this done right.
Rhall -  Could you please to share some screen shots or steps to do this.
I am constantly working on this for last 40 hours without sleep and completely exhausted. It would be better if Talend could share more details in palletes description so we newbie could understand it too.