topic Re: How to get the values dynamically in tOracleInput query in Talend Studio

How to get the values dynamically in tOracleInput query

Anonymous — Tue, 24 Jan 2017 05:59:33 GMT

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

Re: How to get the values dynamically in tOracleInput query

Anonymous — Tue, 24 Jan 2017 09:36:39 GMT

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.

Re: How to get the values dynamically in tOracleInput query

Anonymous — Fri, 27 Jan 2017 06:42:27 GMT

Hi Richard,
Thank you for fast response
I have written the query the way you told, but did not mention properly
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!!

Re: How to get the values dynamically in tOracleInput query

Anonymous — Fri, 27 Jan 2017 09:33:03 GMT

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.

Re: How to get the values dynamically in tOracleInput query

Anonymous — Fri, 27 Jan 2017 13:32:00 GMT

so,Let me take an scenario,
Table - Employee

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!!

Re: How to get the values dynamically in tOracleInput query

Anonymous — Tue, 31 Jan 2017 06:51:43 GMT

Hi,Can anyone help with writing dynamic query with above scenario

Re: How to get the values dynamically in tOracleInput query

Anonymous — Tue, 31 Jan 2017 09:26:56 GMT

Just use your context variable in the SQL query.....

"SELECT 
COLUMN1,
COLUMN2,
COLUMN3
FROM TABLE
WHERE COLUMN1 = " +context.YourContext

Re: How to get the values dynamically in tOracleInput query

Anonymous — Mon, 13 Mar 2017 13:02:57 GMT

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.

Re: How to get the values dynamically in tOracleInput query

cterenzi — Mon, 13 Mar 2017 17:20:59 GMT

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?

Re: How to get the values dynamically in tOracleInput query

_AnonymousUser — Mon, 13 Mar 2017 19:05:50 GMT

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

Re: How to get the values dynamically in tOracleInput query

_AnonymousUser — Mon, 13 Mar 2017 19:17:42 GMT

Hi Team,
Is there any option to get the desire output without creating a table or file and then joining using tMap etc ...
So far all I can see that there is so much buzzz on internet about Talend but it is not even as half good as Pentaho , Pentaho has very less components but it does its job( almost all stuff we can do on Talend) very easy and fast and efficiently , while in Talend we have more then two hundred Palettes but to perform each action we have to use many of them ...
I am sorry if I said something wrong or hurt any Talend lover but after few months of study in Talend and still getting such SILLY issues , I am really pissed off ....
If any option is available for this please let me know....

Re: How to get the values dynamically in tOracleInput query

cterenzi — Mon, 13 Mar 2017 20:22:53 GMT

Instead of asking the database to manipulate your string, do that work in a Talend component and construct the IN clause for the database. The tAggregateRow component can turn many rows of strings into a comma-separated list for you. Barring that, your tJavaFlex probably gets you very close to where you want to be. A second tJavaFlex or a tMap to take off the extra comma may be all you need to eliminate the SELECT 'long literal' FROM DUAL from your query.

Re: How to get the values dynamically in tOracleInput query

Anonymous — Tue, 14 Mar 2017 08:53:23 GMT

Hi Cterenzi/others,
Is there any option for bulk select ?
I already lost my hope , so don't want to waste more on same concept unless until someone tells me its possible to pass variable in such scenario ...

Re: How to get the values dynamically in tOracleInput query

vapukov — Tue, 14 Mar 2017 09:39:31 GMT

bulk SELECT? What are You mean?
excellent tool from Pentaho ... if it so good, why You are there?
each tools have it own drawbacks (and Pentaho very far from be named Best of The Best)
If You do not want save data to DB - save it to buffer (memory) and then use as input flow on feature steps

Re: How to get the values dynamically in tOracleInput query

Anonymous — Tue, 14 Mar 2017 10:12:10 GMT

bulk SELECT? What are You mean?
-- I mean do we have any option to send in parameter as bulk/clob data to avoid such issues?
excellent tool from Pentaho ... if it so good, why You are there? -- What are you , 7!!!!
each tools have it own drawbacks (and Pentaho very far from be named Best of The Best)
-- Its not drawback , it is elementary for any ETL to send send data from source to target and based on inputs source will perform some other operation.
-- In other words you are accepting , it can't do this
If You do not want save data to DB - save it to buffer (memory) and then use as input flow on feature steps
-- Now that is waht I call solution.
-- I will try that .
-- You should shut me down by giving some solutions like this.
-----------------------------------------------
I will search and try to input data to buffer and to run this in select statement .
I will let you know the output .

Re: How to get the values dynamically in tOracleInput query

Anonymous — Tue, 14 Mar 2017 10:45:45 GMT

Amit, your problems come from not understanding the tool I'm afraid. I'm sure if I were to switch to Pentaho and expect it to work like Talend or Informatica, I would be cross because it doesn't work as I expect it to.
It should also be pointed out that " java.sql.SQLSyntaxErrorException: ORA-01704: string literal too long" is an issue with your SQL. You would get the same in Pentaho. Since the data is not already in your database, you cannot inject into your SQL query unless you break it down (the query) or load the data to the db. This is NOT a Talend problem, it is a problem with how you are trying to solve this.
To get round this you can do your filtering in Talend, but as an experienced DI developer I am sure you know that would not be efficient. Your database is designed to do this work. So if you want an efficient solution (in ANY DI tool) you will load the filter data into your database and get the database to earn its place in your data center and do the filtering there. If there is a reason why you cannot do that (and I understand there may well be) then you will have to take a performance hit and filter in Talend, in memory. This is pretty easy to be honest. Take a look at the tHash components ( https://help.talend.com/search/all?query=tHashInput&content-lang=en).

Re: How to get the values dynamically in tOracleInput query

Anonymous — Tue, 14 Mar 2017 14:06:15 GMT

Hi cterenzi/Rhall,

Thanks for your help.
Now I am trying to do some work around. ( Already tried that before , but couldn't succeed )
What if I run this query into tOracleRow ?
-------------------------------------------------------------------------------------------------------------------
( please ignore errors if there is any)
-------------------------------------------------------------------------------------------------------------------
declare
var_input clob := trim(',' from regexp_replace( ' " + context.output_unmatched + " ' ,']',null)) ;
var1 varchar2(4000);
vqr2 varchar2(4000);
var3 varchar2(4000);
var4 varchar2(4000);
vqr5 varchar2(4000);
var6 varchar2(4000);
begin
for i in (select tns from (
select null as tns from dual union all
(select regexp_substr(var_input,'+', 1, level) from dual
connect by regexp_substr(var_input, '+', 1, level) is not null)
) where tns is not null)
select col1 ,col2 ,col3
into var1,vqr2,var3
from my_table
where col1 in (i.tns);
var4 := var4 ||','|| var1;
var5 := var5 ||','|| var2;
var6 := var6 ||','|| var3;
end loop;
end;
-------------------------------------------------------------------------------------------------------------------
Is there any way , I can catch the values of var4 and var5 and var6 into context variables???
can we use output and send this to next step.
!!!!!!!!!!!
I am testing the possibilities for this. Hope this thing works

Re: How to get the values dynamically in tOracleInput query

Anonymous — Tue, 14 Mar 2017 14:24:53 GMT

I'm not sure why you are carrying out string manipulation in SQL, but whatever floats your boat. Why not wrap this into a stored proc and call that ( https://help.talend.com/search/all?query=Calling+a+stored+procedure+or+function&content-lang=en)

Re: How to get the values dynamically in tOracleInput query

Anonymous — Tue, 14 Mar 2017 15:33:30 GMT

I'm not sure why you are carrying out string manipulation in SQL, but whatever floats your boat.
Because I am a sql developer
Why not wrap this into a stored proc and call that ()

I think I am your FAN now.
I will try this. I was hoping to not put anything in DB.But whatever works right...
I have requested a DB link so I can change this to SP.
Thanks for the help , I will continue on this tomorrow .

Re: How to get the values dynamically in tOracleInput query

cterenzi — Tue, 14 Mar 2017 17:08:32 GMT

I'm still convinced that your problem is being caused by passing a very long string literal instead of constructing a well-formatted list for your IN clause. The only reason you're passing a string seems to be that you need to remove an extra comma from your list. This is not difficult to solve in Talend. Once you have your list of values nicely formatted as:
val1, val2, val3, val4...
...in a context or global variable, you can drop it right into a SQL statement:
"select Col1, col2, col3, col4
from my_table
where col1 in ("+context.output_Tns +"); "

If your input data is typed as String, passing it through a tAggregateRow component using the List operator can do this for you. Otherwise, once you've finished reading in all your data and building your variable, a triggered tJava component can clean up the string before triggering your tOracleInput.