tFlowToIterate to Dynamic SQL in tOracleInput not executing properly
Hi Guys, I'm a new to Talend (about a week or so).
I've been trying different things with this scenario and when it looked like I got it working... it wasn't really working.
So here's the deal.
from on tOracleInput I'm reading distinct dates. With these dates I'm putting it through a tFlowToIterate so they can be run as a parameter one at a time in another tOracleInput. (this is very similar to other postings so far.).
However I was having trouble witht he second tOracleInput reading my syntax, so I between the tFlowToIterate and the following tOracleInput I handled the Dynamic SQL in a tJava component (I'm not a Java developer).
in tJava I used the globalMap.put method with the key for my second tOracleInput_1_QUERY and the value being my dynamic SQL (complex).
I'll just show a piece of the where clause here.
"Where tr.TRADEDATE='"+ (TalendDate.formatDate("dd-MMM-yy 12.00.00.000000000 'AM'",(java.util.Date)globalMap.get("row1.TradeDate"))) + "' GROUP BY etc..."
To see if this was working I followed something in the user Guide to log the state on my second tOracleInput_QUERY value with a scone tJava.
Now when I run this. I see that the value in my second tOracleInput_Query changing as expected. However the only query that is executing is the original one I used to configure the second tOracleInput not the dynamic SQL. I know this because there are no rows from the output of the second tOracleInput, but when I take the code from the log and execute it directly in Oracle I get records.
So what am I missing?
Oh! from the first tJava I've played with both the OncomponentOk and the Iterate flows and both have the same effect.
I reread this and realized I missed a piece of information that might help. In the tJava, the actual key I'm using is AssetFlows_QUERY: globalMap.put("AssetFlows_QUERY","Select ea.Entityid dimContactId, etc.... I also tried: globalMap.put("tOracleInput_2_QUERY","Select ea.Entityid dimContactId, etc... but this didn't seem to work with the iterate... even though I could see the iterate executing many different rows, the date value in my dynamic query remained the first value. I thought you might want the actual keys incase I'm not using the correct one.
Ok I've got it.
This is resolved.
globalMap.put doesn't work the way I'm using it.
I had to create the variable context.DynamicQuery then assign my SQL in the tJava to this.
Then in the tOracleInput_2 I just referenced context.DynamicQuery in the QUERY parameter.
It's running... it's slow but it's running.
Can you please provide end to end approach to arrive the solution for such scenarios ? This helps to understand clearly about Dynamic Select statements execution using TOS.
