How to pass the output of one oracle sql to other oracle sql in talend

[Anonymous]

I am trying to create the 2 sqls. In first sql my output will be the last runtime of the Table and in another sql I want to feed this output as input for 2nd sql. I am trying to pass the variable through Tflow to iterate component. But here it is taking the default date (Java.util.date) as the input to my second sql and getting the error as invalid relation operator.
I need to pass the date format as "dd-MMM-YY hh:mm:ss:ssz" in the second sql but whereas from the tflow to iterate component giving the dateformat as "Mon Apr 08 22:00:06 IST 2019". 

Please suggest how to fix this in flowto iterate component and provide the suggestion on if any other component is there to iterate the values ?

[akumar2301]

Can you give us some screen shot of job design

[vapukov]

Hi,

you could format date to formatted string and use it in the query instead of date

TalendDate.formatDate("dd-MMM-YY hh:mm:ss:ssz",myDate)

so, query will be like:

(edited)

"SELECT .... FROM ... WHERE DateCol = '"+TalendDate.formatDate("dd-MMM-YY hh:mm:ss:ssz",(Date)globalMap.get("row1.dateCol"))+"'

[Anonymous]

Hi,
Thank you for your response. I had tried the same query as you suggested but getting the error as "The method formatdate (String, Date) in the type Talenddate is not applicable for the arguments (String, String) "
Since I am getting the output from the first Sql as the String and trying to convert into different format I am getting this error.

If I am giving as parsedate instead of format date then I am getting this error
"The method get (object) in the type Map<string,object> is not applicable for the arguments (String, Date)

The query I tried is
"SELECT .... FROM ... WHERE DateCol = '"+(String)globalMap.get("row1.dateCol",TalendDate.parseDate("dd-MMM-YY hh:mm:ss:ssz",row1.dateCol))+"'

Please suggest me how I can resolve these

[vapukov]

oh, sorry

I answer without a computer, so of course, make a mistake 

my variant must be like:

"SELECT .... FROM ... WHERE DateCol = '"+TalendDate.formatDate("dd-MMM-YY hh:mm:ss:ssz",(Date)globalMap.get("row1.dateCol"))+"'

but in case if you iterate over string - you can use just :

"SELECT .... FROM ... WHERE DateCol = '"+(String)globalMap.get("row1.dateCol")+"'

if your String variable not in a proper format - then you need first:

- parse String to Date using your original string date pattern

- format Date to String using standard (expected) date pattern

[Anonymous]

I tried tmap with the format date and parse date and then tried to map it to the Tflowtoiterate component..But from Tflowto iterate component it is just taking String value (default format)
My Flow in the Talend is
DBInput1 --> Tlogrow --> TFlowtoiterate --> DBInput2
I tried this flow also,
DBInput1 --> Tlogrow --> Tmap ( coverted the string to desired format) --> TFlowtoiterate --> DBInput2

In this flow, the global variable is taking the default string value. Let me know if the flow is wrong

[vapukov]

don't look for tLog row - it just prints default date format, 

tFlowToIterate support all formats, so you can send data as Date 

tDBInput1 -> tMap (parseDate from String) -> tFlowToIterate ->  tDBInput2 (there use formatDate from (Date)globalMap.get()))

[Anonymous]

Hi,
Thank you.. I understood the flow... Could you please explain me more with the syntax format of each... The last input for dbinput2 is bit confusing

[vapukov]

1) IF your source column is already date (don't worry about the pattern, just must be defined as Date) - just go to step 3

2) if your source column is String

tMap after tDBInput1 - you MUST use proper pattern which you have in source String

TalendDate.parseDate("EEE, d MMM yyyy HH:mm:ss Z",row1.dateCol)

good link for test - http://www.sdfonlinetester.info/#

3) 

tDBInput2 (below your original format)

"SELECT .... FROM ... WHERE DateCol = '"+TalendDate.formatDate("dd-MMM-YY hh:mm:ss:ssz",(Date)globalMap.get("row1.dateCol"))+"'

[Anonymous]

Hi,
Thank you. I will try to implement the same logic