One Star

[resolved] NULL records for count(*) in tPostgresql_row

Hi,
I am executing a query using the tPostgressql_row component and passing the results on from that query to tLog row and a tPostgresqlOutput component.
The query is as follows:
"select
count(*),
\"PRS_ID\",
\"DATE\"
from
\"TABLE\"
where
\"public\".\"TABLE\".\"COLUMN_1\" = ?
and
\"public\".\"TABLE\".\"COLUMN_2\" = ?

group by
\"PRS_ID\",
\"DATE\" "

The component is set to use a Prepared Statement and seems to be working fine. The Schema is set to have 3 output rows - count, PRS_ID and DATE. The PRS_ID and DATE column load data just fine but I always get a NULL value for the count column.
While trouble shooting I tried aliasing the DATE column to DATE_D and had the schema column name set to DATE_D. The DATE_D column then contained NULL's too.
I tried populating the result set and then parsing it but the parse component doesn't output any rows - Going through the forums I saw that that is a bug when there is only 1 record in the result set.
Any ideas how I can solve this?
Thanks!
2 ACCEPTED SOLUTIONS

Accepted Solutions
Seventeen Stars

Re: [resolved] NULL records for count(*) in tPostgresql_row

Hi mclarenlife,
That is not true. I use the tParseRecordSet component often.
Both components works in this way:
tPostgresqlRow executes the statement and return NO rows but the ResultSet object. The tParseRecordSet takes this ResultSet and opens it and if this ResultSet provides rows you will get some and if not ... ok than none rows will be returned.
Once again, the tPostgresqlRow component does not any influence how many rows the tParseRecordSet component returns.
If you provide the correct parameters to your query you will get as much rows as you would get in any other SQL based tool.
One Star

Re: [resolved] NULL records for count(*) in tPostgresql_row

Hi jlolling,
Thanks for helping out!
I have attached the screen shots of the way I'm using the tParseRecordSet.
As you can see, no data is propagated out of the tParseRecordSet!
Help Smiley Happy
10 REPLIES
Community Manager

Re: [resolved] NULL records for count(*) in tPostgresql_row

Hi
tXXXRow component is usually used to execute a SQL statement except select statement, we use txxxInput such as tPostgresqlInput, to select data from a table. In your case, you can define a dynamic query with context variables in tPostgresqlInput, for example:
"select
count(*),
\"PRS_ID\",
\"DATE\"
from
\"TABLE\"
where
\"public\".\"TABLE\".\"COLUMN_1\" ="+context.column_1+"
and
\"public\".\"TABLE\".\"COLUMN_2\" = "+context.column_2+"

group by
\"PRS_ID\",
\"DATE\" "
And pass value to context variables on-the-fly.
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Seventeen Stars

Re: [resolved] NULL records for count(*) in tPostgresql_row

If you want to stay with the prepared statement parameters - I would like you to encourage - you have to use the component tParseRecordSet.
tPostgresqlRow --> tParseRecordSet
In the tPostgresqlRow you need only the schema columns to fill the prepared statement parameters and for the output you need only one column of type Object which receives the ResultSet object. The tParseRecordSet iterates through the ResultSet and extracts the values (by name of the fields in the query).
One Star

Re: [resolved] NULL records for count(*) in tPostgresql_row

@shong;
How would I use an tPostgresqlInput such that it executes on every incoming row in the flow? I'm not able to drag a 'main row' out of a component into an t**Input component?
What I'm trying to achieve is to calculate a count of occurrence for each incoming ID in an incoming row.
@jlolling;
I first tried the record set method. The problem with the record set is that it will not break up the record set if there is just 1 row in the result. This is documented on other posts in this forum. If I run a select * query, the tParseRecordSet component works great - though if the select * query returns 1 row the tParseRecordSet does NOT parse the record and propagate the row.
Thanks!
Seventeen Stars

Re: [resolved] NULL records for count(*) in tPostgresql_row

Hi mclarenlife,
That is not true. I use the tParseRecordSet component often.
Both components works in this way:
tPostgresqlRow executes the statement and return NO rows but the ResultSet object. The tParseRecordSet takes this ResultSet and opens it and if this ResultSet provides rows you will get some and if not ... ok than none rows will be returned.
Once again, the tPostgresqlRow component does not any influence how many rows the tParseRecordSet component returns.
If you provide the correct parameters to your query you will get as much rows as you would get in any other SQL based tool.
One Star

Re: [resolved] NULL records for count(*) in tPostgresql_row

Hi jlolling,
Thanks for helping out!
I have attached the screen shots of the way I'm using the tParseRecordSet.
As you can see, no data is propagated out of the tParseRecordSet!
Help Smiley Happy
One Star

Re: [resolved] NULL records for count(*) in tPostgresql_row

In order to make sure that the query is actually returning data I used a tReplicate and another postgresqlRow component like shows.
The output in the tLogRow component shows NULL's for the count(*) but contains data for the other 2 columns.
Seventeen Stars

Re: [resolved] NULL records for count(*) in tPostgresql_row

I do not know whats wrong with your job but normally this design should work.
But because you do not use prepared statements you can simplify your job with the tPostgresqlInput component.
One Star

Re: [resolved] NULL records for count(*) in tPostgresql_row

I'll give prepared statements another shot - thats how I had it running earlier.
How would i run this using a tPostgresqlInput given that I need to have it execute on every row?
One Star

Re: [resolved] NULL records for count(*) in tPostgresql_row

jlolling,
I would be very careful when using the t**row component. I just confirmed that it doesn't pass on any rows when there is 1 row in the result set.
I added this to my query:
UNION ALL
SELECT count, PRS_ID, DT FROM (
SELECT 4 AS count, 'o'::text AS PRS_ID, '2014-02-02':Smiley Very HappyATE AS DT) AS TEMP
Which basically creates a dummy row of data. I then filter this row out after the tParseRecordSet before writing.
Everything works great now. But this is definitely a bug as the behavior is not documented anywhere.
Thanks for your help and I hope this thread helps someone down the road.
One Star

Re: [resolved] NULL records for count(*) in tPostgresql_row

I do not know whats wrong with your job but normally this design should work.
But because you do not use prepared statements you can simplify your job with the tPostgresqlInput component.

Hi jlolling,
The method you suggested here to use txxxRow and tParseRcord set works great. However, I have a scenario like :
Say input is 100 records to txxxRow and in the select SQL of txxxRow finds a match for Only 90 records. So, it passes only 90 to tParseRecorset.
Question is :
                   How to collect that 10 UNUSED records ? Can you please sugest some way around.
Thanks,
Vimal