talend - output of tMap to another query

Question

I have a one view query (which is quite heavy) so I want to avoid re-querying again.

The output of this query is transformed and put into the file. There is a unique reference number on this file (field reference in the query).

The "references" I need as an input as a where clause in my second query.

I'm thinking of this flow: 1st subjob:

                                  tOutputFile     
                                   /   
tOracleInput ->  tMap -> tReplicate
                                   \
                                  tMap (will only map the reference field)
                                     \
                                     tSetGlobalVar 
                                        (set to a list, and add to globalMap)

And upon complete of that subjob, the next subjob will run;

tOracleInput (build the where clause from the list from globalMap) -> tMap  -> tOutputFile

Does this design looks okay? Or am I better off using a subquery on the references number in my 2nd tOracleInput?

SELECT ... FROM table1 WHERE references  IN (SELECT references from BIGVIEW WHERE ...)

score 0 · Answer 1 · answered Jun 22 '17 at 19:43

Depending on how many different values are retrieved for the reference field, the query should exceed the maximum length authorized by Oracle.
You should consider to join these values with the 2nd tOracleInput using facilities offered by "Reload at each row" lookup model.
Lear how it works here.

Hope this helps.

talend - output of tMap to another query

1 Answers1