topic Re: Read data form a database function until return no rows in Talend Studio

Read data form a database function until return no rows

Anonymous — Wed, 27 Mar 2019 11:36:55 GMT

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

Re: Read data form a database function until return no rows

akumar2301 — Wed, 27 Mar 2019 14:11:56 GMT

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.

Re: Read data form a database function until return no rows

Anonymous — Wed, 27 Mar 2019 15:03:48 GMT

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?

Re: Read data form a database function until return no rows

akumar2301 — Wed, 27 Mar 2019 15:58:10 GMT

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?

Re: Read data form a database function until return no rows

vapukov — Thu, 28 Mar 2019 11:59:47 GMT

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

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

Re: Read data form a database function until return no rows

Anonymous — Fri, 29 Mar 2019 12:19:19 GMT

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

Re: Read data form a database function until return no rows

akumar2301 — Fri, 29 Mar 2019 12:41:33 GMT

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

Re: Read data form a database function until return no rows

Anonymous — Fri, 29 Mar 2019 12:47:36 GMT

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

Re: Read data form a database function until return no rows

akumar2301 — Fri, 29 Mar 2019 12:55:10 GMT

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

Re: Read data form a database function until return no rows

Anonymous — Fri, 29 Mar 2019 13:03:20 GMT

Thanks, that works