Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to get the values dynamically in tOracleInput query

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
Labels (2)
38 Replies
Anonymous
Not applicable
Author

SQL queries in all of the Talend database components are simply Java Strings. As such you can build them dynamically. However you must make sure that the built String represents legitimate SQL.
Looking at your example.....
"Select * from table1" +context.filename

....I can immediately seem a possible error in that you have not left a space after "table1". When you concatenate that String with ...
.... you will get....
"Select * from table1where col3 in (val1,val2, val3)"

I also notice that the "vals" are not formatted. For numbers that is OK, but if they are representing Strings (for example), you will need to add quotes, etc. 
The best way to approach this is to build your SQL query, output it to the System.out and then test the String you see in a query analyser. If it works there, it will work in your DB component.
Anonymous
Not applicable
Author

Hi Richard,
Thank you for fast response
I have written the query the way you told, but did not mention properly  0683p000009MPcz.png
It is throwing error 
Could you please suggest and provide a sample method (best) for dynamic query where i have to pass 20000 records dynamically at IN clause
I used the method suggested by you in the below post
But, this is row wise processing(it is consuming more time when I have filter in more 2 or more columns, Suppose if I have 4 filter conditions in Column A and 4 filter conditions in Column B, then I have to give 16 combinations of input)
I need some method where the query is build using the input from file
Hope you get my requirement!!
Anonymous
Not applicable
Author

I'm afraid you will need to give examples of the sort of queries you will need. There is not enough info here. However, the example I gave should be enough to be extrapolated from. 
Just think about building a String in Java.

String select = "Select column1, column2, column3 From table1 ";
String where = "";
if(row1.value.compareToIgnoreCase("test")==0){
where = "Where column1 = '"+row1.data+"'"
}
String query = select+where;
System.out.println(query);

The above will produce a query with a where clause of "column1 = " whatever is held by the data column IF the value column holds "test". Otherwise it will produce a query without a Where clause.
The "System.out.println" code allows you to output the query to the output window. You can use that to see it as a String, copy and paste it into a query analyser and test it on your db.
Anonymous
Not applicable
Author

so,Let me take an scenario,
Table - Employee
0683p000009MCvk.png
I need to put the query in tOracleInput as 
Select * from Employee where DESIGNATION in ('Developer', 'Analyst') and SALARY in (3000)
But, here I need to give the where clause dynamically(through a text file)
i.e., Select * from Employee +context.file
so, I can change the filters whenever I need without changing the job
I need to know how to append to query through context(in file)
Hope it helps!!
Anonymous
Not applicable
Author

Hi,Can anyone help with writing dynamic query with above scenario
Anonymous
Not applicable
Author

Just use your context variable in the SQL query.....
"SELECT 
COLUMN1,
COLUMN2,
COLUMN3
FROM TABLE
WHERE COLUMN1 = " +context.YourContext
Anonymous
Not applicable
Author

Hi Guys ,
I too have problem while using tOracleInput.
I am taking data from excel into context variable output_Tns (tried all data type available).
My talend job looks like
(tFileInputExcel -- tJavaRow (context.output_Tns = context.output_Tns  + "," + input_row.Unmatched_Tns) -- tOracleInput --tJavaRow  -- tSendMail )
then I am pass the variable data into select statement inside tOracleInput
"select Col1 ,
          col2 ,
          col3 ,
          col4
   from my_table
 where col1 in (select replace(trim(',' from dbms_lob.substr(' "+context.output_Tns +" ', 4000, 1 ) ),' ','')
                       from dual)"
----------------------------------------------------------
While running the job I am getting below error :
                                              Exception in component tOracleInput_2
                                              java.sql.SQLSyntaxErrorException: ORA-01704: string literal too long
I know it is because variable length is more then 4000 , but do we have any work around to run this query . Later I am catching these columns into other variables too.
Please help , I am new in talend and don't know how to continue further. 0683p000009MPcz.png
cterenzi
Specialist
Specialist

Instead of building a "WHERE col1 IN" clause with your list of unmatched values, why not insert them into a table and join it to your my_table?
_AnonymousUser
Specialist III
Specialist III

Instead of building a "WHERE col1 IN" clause with your list of unmatched values, why not insert them into a table and join it to your my_table?

Thanks for this work around , but why do  I need to put data physically in any DB.What if we are running same job in parallel with different excel files ? It should be independent of any DB. Pentaho has less palettes and it provides option pass variables directly to the query from any input files. If such option is present in Pentaho , it should be present in Talend too.
Anyways thanks for help 0683p000009MACn.png