topic select count(*) from tablename where key = <parameter> in Talend Studio

select count(*) from tablename where key =

Frans1 — Sat, 16 Nov 2024 04:04:31 GMT

I want to do this simple query in Talend, but until now (2 days later) I haven't make this query in Talend working. What I want is the amout of records with a where clause with only one parameter. This parameter is available in context, and I want to work with the result, the amout of records futher on in the flow. How can I do this.

I am new within Talend so I think it is an simple question four the experienced users among us but not fot me please help !!

Re: select count(*) from tablename where key =

Anonymous — Wed, 20 Nov 2019 09:47:18 GMT

Hi
Quick question, what is the data type of parameter? Date or Integer or String?
Assuming you are using Mysql database, you can use a tMysqlInput component to execute this query, eg:
"select count(*) from tableName where key="+context.parameterName

Regards
Shong

Re: select count(*) from tablename where key =

Frans1 — Wed, 20 Nov 2019 10:26:22 GMT

What a quick response great !!!

We are using a Postgres database and the parameter is a string.

This is what I use at this moment:

PostgresInput.docx

Re: select count(*) from tablename where key =

Anonymous — Wed, 20 Nov 2019 14:46:57 GMT

Hi,

I could see that equal to character is missing in your query.

Also you are using escape character and you have not closed the original query.

select count(*) as AANTRECS from tablename where cipversienummer =\""+context.variable+"\""

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

Re: select count(*) from tablename where key =

Frans1 — Thu, 21 Nov 2019 09:49:27 GMT

Hi Nikhil,

I have changed the statement as you suggested I just added one " at the begin because otherwise I get errors, this is the SQL-statement what is executed.

"select count(*) as AANTRECS from ldf.ibz_cip_cipersinformatie where cipversienummer =\" "+context.VRSNUM +"\""

And I get the next response with the error that 010108 is not a column. '010108' is the value of context.VRSNUM and that is what want but at runtime I get an error. How can I solve this, so that the query will be excuted with the value from context.VRSNUM

Starting job ibz_cipers_read_versieinformatie1 at 10:44 21/11/2019.

[statistics] connecting to socket on port 3498
[statistics] connected
tContextLoad_4 set key "VRSNUM" with value "010108"
Exception in component tPostgresqlInput_2
org.postgresql.util.PSQLException: ERROR: column " 010108" does not exist
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:336)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:235)
at irapportage.ibz_cipers_read_versieinformatie1_0_1.ibz_cipers_read_versieinformatie1.tPostgresqlInput_2Process(ibz_cipers_read_versieinformatie1.java:4142)
at irapportage.ibz_cipers_read_versieinformatie1_0_1.ibz_cipers_read_versieinformatie1.tMsgBox_2Process(ibz_cipers_read_versieinformatie1.java:3110)
at irapportage.ibz_cipers_read_versieinformatie1_0_1.ibz_cipers_read_versieinformatie1.tMsgBox_1Process(ibz_cipers_read_versieinformatie1.java:2989)
at irapportage.ibz_cipers_read_versieinformatie1_0_1.ibz_cipers_read_versieinformatie1.tAS400Input_1Process(ibz_cipers_read_versieinformatie1.java:2846)
at irapportage.ibz_cipers_read_versieinformatie1_0_1.ibz_cipers_read_versieinformatie1.tFileInputDelimited_2Process(ibz_cipers_read_versieinformatie1.java:1944)
at irapportage.ibz_cipers_read_versieinformatie1_0_1.ibz_cipers_read_versieinformatie1.tFileInputDelimited_1Process(ibz_cipers_read_versieinformatie1.java:1323)
at irapportage.ibz_cipers_read_versieinformatie1_0_1.ibz_cipers_read_versieinformatie1.runJobInTOS(ibz_cipers_read_versieinformatie1.java:5723)
at irapportage.ibz_cipers_read_versieinformatie1_0_1.ibz_cipers_read_versieinformatie1.main(ibz_cipers_read_versieinformatie1.java:5432)
[statistics] disconnected
Job ibz_cipers_read_versieinformatie1 ended at 10:44 21/11/2019. [exit code=1]

Regards,

Frans

Re: select count(*) from tablename where key =

Frans1 — Thu, 21 Nov 2019 14:26:43 GMT

Hi Nikhil,

I have changed the statement as you suggested I just added one " at the begin because otherwise I get errors, this is the SQL-statement what is executed.

"select count(*) as AANTRECS from ldf.ibz_cip_cipersinformatie where cipversienummer =\" "+context.VRSNUM +"\""

Starting job ibz_cipers_read_versieinformatie1 at 10:44 21/11/2019.

Regards,

Frans

Re: select count(*) from tablename where key =

Anonymous — Thu, 21 Nov 2019 15:16:22 GMT

Hi,

It seems you have not added the escape character before the starting of the string. Could you please add it as shown in my previous post?

Please also try to print and see the entire select statement using System.out function in a tjava to make sure that the select statement is correctly generated.

Warm Regards,

Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

Re: select count(*) from tablename where key =

Frans1 — Fri, 22 Nov 2019 14:14:33 GMT

Hi Nikhil,

I think i have solved the problem.

"select count(*) as AANTRECS from ldf.ibz_cip_cipersinformatie where cipversienummer = '" + context.VRSNUM + "'"

result in on runtime:

select count(*) as AANTRECS from ldf.ibz_cip_cipersinformatie where cipversienummer = '010108'

and the query seems to be working, but where can I find the result of the query?

I expected this in context.AANTRECS but this still 'null' after the excecute, how can I acces the value of the result of the query I should expect AANTRECS

but this still 'null' after the query but to contence shoud be 1 or higher

Thank you in advance

Frans

Re: select count(*) from tablename where key =

Anonymous — Fri, 22 Nov 2019 15:54:57 GMT

Hi,

You are right. I overlooked it. Ideally you need to use single quotes. Now coming to your current issue, I would recommend to add a tLogrow after tDBInput to check whether data is coming out from DB component. Once its, confirmed, you can check whether you are passing the data properly. You do not need any component like tMsgBox and tJava between Context Load and tMap. Once the data is mapped correctly, it will be loaded to corresponding context variable.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

Re: select count(*) from tablename where key =

Frans1 — Mon, 25 Nov 2019 14:11:58 GMT

Hi Nikhil,

First of all sorry for all the questions !!

I have added tLogRow and then I see a result from the query, in the picture is this 0 and that is correct. As you can see I have added tContextload and after that I want to execute the next job when the value of context.AANTRECS = 0 (null). But that isn't working this is component value of the if-statement after the contextload:

context.AANTRECS.equals('0')

Can you tell me what I'am doing wrong?

Re: select count(*) from tablename where key =

Frans1 — Mon, 25 Nov 2019 14:17:39 GMT

Nikhil,

I have added an other picture witg information from a debug run, may be this will help.

Regards,

Frans

Re: select count(*) from tablename where key =

Frans1 — Mon, 25 Nov 2019 14:43:10 GMT

Nikhil,

Herewith more inofrmation, I think i saw some information wrong. In the query I do a 'select count(*) from ....' so I expect only 1 (one) colomn.

As you can see in the picture I think al the coloms are returned + the extra count(*) field in the name of AANTRECS. This is not what I expected and may this is the course of the problem?

Re: select count(*) from tablename where key =

Anonymous — Mon, 25 Nov 2019 14:51:32 GMT

Hi,

Could you please try like below?

I believe you are using On Component Ok instead of On SubJob Ok which is trigger the job before the previous job is getting completed fully. Please use On Component Ok only if you need (like in the case of condition check in iterative format).

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

Re: select count(*) from tablename where key =

Frans1 — Mon, 25 Nov 2019 15:09:36 GMT

Hi Nikhil,

And what must I place in the 'IF condition' after tJava ?

context.AANTRECS.equals ("null") or context.AANTRECS.equals ("0") or context.AANTRECS.equals ('0') doesn't work.

I expect that in tJava something like:

context.AANTRECS = < result from query AANTRECS>

has to be placed because the value of AANRECS from the query has to be put in the variable 'context.AANTRECS'

Re: select count(*) from tablename where key =

Frans1 — Tue, 26 Nov 2019 11:46:56 GMT

I don't think a simple query like

select count(*) as context.AANTRECS

will work in Talend !!!

This is very dissapointing for me and my collegae's.

Re: select count(*) from tablename where key =

Anonymous — Wed, 27 Nov 2019 00:54:38 GMT

Hi,

Unfortunately you are thinking that its not possible in Talend without going through Talend learning path. There are lot of customers who do much complex things using Talend in seamless fashion. I would recommend to learn Talend in a structured way using Talend academy if you are not able to scale quickly using help documents alone.

Coming to your earlier query, since the context variable is Integer, you will have to use Integer operations within if condition. Right now, you are trying String operations in if conditions.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

Re: select count(*) from tablename where key =

Frans1 — Wed, 27 Nov 2019 13:23:56 GMT

Hi Nikhil,

I have already have used the integer and String 'variant' and I know that in the example this is wrong. But my problem is that i can't validate the value of AANTRECS after the query is executed. With tLogrow i can see the result of the query but I want to use this result for a further decision so this has to put in context.AANTRES. I expect this value in context.AANTRECS but in debug-mode the value is always 'null' even if the result of count(*) is > zero. Is there an otherway the determine if a record already exits in a table, i have tried it with 'component error' and 'subjob-error' but that seems not to be working as I expect it.

And you're wright tah I have, and will, follow the courses of Talend.

best regards,

Frans

Re: select count(*) from tablename where key =

Anonymous — Thu, 28 Nov 2019 01:33:16 GMT

Hi Frans
If you want to specify the query result to a context variable, we usually do it on tJavaRow component (DO NOT use tJava component), eg:
tPostgresqlInput--main--tJavaRow...

on tJavaRow:
context.AANTRES=input_row.columnName;

Regards
Shong

Re: select count(*) from tablename where key =

Frans1 — Mon, 02 Dec 2019 11:03:27 GMT

Hi Shong / Nikhil,

This is what needed great !!!

Can you advise me what course I can follow to learn more about Talend?

As you may have noticed I have a lot of experience but I am new with Talend so please advice.

many regards for all your help

Frans