Five Stars

How to retrieve the row count from a table?

Hi all,
I need to retrieve the row count of a table. How can I do this? 
For this I should execute a select query to the database (Oracle in my case).
Can I use the tOracleRow component to get this done? If so, please advice me.
Thanks,
thivanka.
14 REPLIES
Moderator

Re: How to retrieve the row count from a table?

Hi,
Do you want to capture no. of insert, update, and delete for a table? If so, you can add a tFlowMeter component between input component and output component, and then using tFlow MeterCatcher to catch the no of records.
 
For example:
tFileInput--->tFlowMeter--->tMysqlOutput_1
tFlowMeterCatcher---tLogRow
There is one column called count on the schema of tFlowMeterCatcher which counts the no. of records pass by the specify flow.
Let me know if it is OK with you.
 
Or you want to count the number of rows are inserted/updated/deleted? Global variable, such as:
((Integer)globalMap.get("tOracleOutput_1_NB_LINE_INSERTED"))?
 
Best regards
Sabrina
 
 
 
 
 
 
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Five Stars

Re: How to retrieve the row count from a table?

Hi Sabrina,
Thanks for your quick reply. But, what I need is not the things you mentioned. For example, say that a table
contains 1548 records. I need to get the number of rows in the table to an integer variable or something. So the
value I want is 1548.
Thanks,
thivanka.
Moderator

Re: How to retrieve the row count from a table?

Hi,
For example, say that a table
contains 1548 records. I need to get the number of rows in the table to an integer variable or something. So the
value I want is 1548.

Do you mean you want to use SQL COUNT() function in talend DB component?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Five Stars

Re: How to retrieve the row count from a table?

Hi Sabrina,
Yeah, I need to use the SQL COUNT().
Five Stars

Re: How to retrieve the row count from a table?

Hi Sabrina,
Is there any solution for this?
Thanks,
thivanka.
One Star

Re: How to retrieve the row count from a table?

I suppose the crude solution to your problem would be to use a tOracleInput with a select count that you iteratetoflow and use in your job... Not very cool, but would work.
Five Stars

Re: How to retrieve the row count from a table?

Yes, but I was thinking whether we can use a tOralcleRow and execute the SQL COUNT() command in it and retrieve the value from it.
Since the tOracleRow returns a ResultSet, I dont know how to get the count from it. 
Moderator

Re: How to retrieve the row count from a table?

Hi,
Sorry for delay.
tXXXRow component is usually used to any type of sql statement excepts select statement,  we use txxxInput component to execute a select statement, if you use use tXXXRow to execute a select statement, it returns a record set, and you are required to use a tParseRecordSet component after tXXXRow to parse the record sets.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Five Stars

Re: How to retrieve the row count from a table?

Hi Sabrina,
Thanks for the reply. I will try to do this as you suggested.
Thanks,
thivanka.
Moderator

Re: How to retrieve the row count from a table?

Hi thivanka,
Feel free to let me know if it is OK with you.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: How to retrieve the row count from a table?

Hi thivanka,
tAggregateRow component could help You.
If your using tmap then create another output link to tAggregateRow and select the required function from Operations Property of it.
Attached screen shots. Please find it.




In other you can also use tFlowmeter.
Thanks & Regards
Dasari
One Star

Re: How to retrieve the row count from a table?

Hi Dasari,
Thank you for you support. I will try what you have given and get back to you.
Thanks,
thivanka.
One Star

Re: How to retrieve the row count from a table?

IF the table is really huge (say multi million records), one way  is to get it is from Oracle data dictionary tables.  select num_rows from dba_tables where table_name ='XYZ'.  The catch is that stats need to be collected. Again this solution is if the number of rows is so large that select count(1) from XYZ takes 1/2 hour and does not meet your SLAs
One Star

Re: How to retrieve the row count from a table?

Hi xdshi,
Hi want to capture unused records from my toraclerow component. How can it be done?
Thanks,
Anuj