Read data form a database function until return no rows

[Anonymous]

Hello, 

I'm doing a job that returns from a function limited rows with data.

I have to read the data until the function return no rows, so, the function return 1000 rows each time

example:

I have 2028 rows in the function:

I run the job and the function returns 1000

then

I run again and the function returns 1000

then 

I run again and the function returns 28

then

I run again and the function returns 0

then create the file

How can I do this loop? 

Anyone can help me?

Thanks,

Elisabete

[akumar2301]

You mean , your function returns RecordSet of n Number of rows.If Yes, to execute parse the recordset of tDBxxSP,  you must be using tParseRecordSet. 

You can compare ((Integer)globalMap.get("tParseRecordSet_1_NB_LINE")) in tLoop condition. Run loop only if it is not equal to zero.

[Anonymous]

Hello, 

no, What I want is read data from a tinputDB, but is an database fucntion, and is limited to the number of rows you can read... I need to read it and load to a thashOutput (append) until the function returned zero rows, like ((Integer)globalMap.get("tDBInput_2_NB_LINE"))=0. 

Can you help me?

[akumar2301]

Hello ,

Not able to understand ,"read data from a tinputDB, but is an database fucntion ".

Can you please explain more about this function and if it is a function , how you are planning to use in tDBINPUT?

[vapukov]

Hi Elizabeth,

I agree with @uganesh - it is not clear from your description what is function, how it work and etc

but let take a global case - you have 2028 rows in the database for processing and what process not more than 1000 rows at one iteration

in this case, job design is simple

• first of all - we need or mark rows as processed or store processed rows id in separate table
• then in tDBInput we use query like:

SELECT * FROM table_a WHERE proceessed = 0 LIMIT 1000

or

SELECT * FROM table_a ta WHERE NOT EXISTS (select * from processed_table pt where pt.id = pa.id ) LIMIT 1000

• at the end of job - you are OR UPDATE original table and mark processed id OR just INSERT id into processed_table

that all

regards, Vlad

[Anonymous]

Hello, 

what I want is a loop in tDBInout until the query return no rows. The query is a function, something like "select field1 from fct_example('PUSH')" but this is limited to 1000 rows each time I execute the query and delete the data that has been read (this is what the function do), so if the function has 2028, I have to execute the job as many times as necessary to return no rows. 

Job_Loop.PNG

[akumar2301]

did you try tLoop before tDbInput with condition "while  condition"

((Integer)globalMap.get("tDBInput_2_NB_LINE")) == null ||
((Integer)globalMap.get("tDBInput_2_NB_LINE")) != 0

[Anonymous]

yes, and is always running the job.

Output:

Null value will be used for context parameter queryloop: For input string: ""
[statistics] connecting to socket on port 3640
[statistics] connected

Send the configuration of the loop, I'm newer in talend, I worked with datastage and this is a quiet diferent.

Job_Loop2.PNG

[akumar2301 · Accepted solution]

double check  GlobalMap Variable Name "tDBInput_2_NB_LINE". Is  number in this correct ?

[Anonymous]

Thanks, that works