Print message if SQL returns 0 rows.

[jensej]

Hi!

Let's say I run this SQL query select name, vorname from persons limit 1. Normally it returns data but I want to build some kind of case statement in tmap to return something even if the query return 0 rows.

I tried to use nb_line but that returns null

So my goal is when the query returns 0 rows then I want to add text "No rows returned" in the name column instead of row6.name.

Any ideas of how to achieve this?

[Anonymous]

Hi

The nb_line variable can be only used after the tDBInput component execution. In this case, run the query two times, the first tDBInput is used to check if there has rows returned or not, if no rows returned, output a customized messages, otherwise, run the same query for the second time, read the data from DB and output them, the job looks like:

tDBinput1--mian--tJavaRow

--runIf1--tFixedFlowInput--main--tLogRow1

--runIf2--tDBInput2-main-tLogRow2

set the condition of runtIf1 as:

((Integer)globalMap.get("tDBInput_1_NB_LINE"))==0

on tFixedFlowInput, add one field 'name' and set its value as "No rows returned!"

set the condition of runtIf2 as:

((Integer)globalMap.get("tDBInput_1_NB_LINE"))>0

Regards

Shong

[Anonymous]

Hello @Jens Frejd​ ,

Maybe you can add one 'Run If' trigger with condition ((Integer)globalMap.get("tDBInput_2_NB_LINE"))==0 for the tDBInput component like the below , in the flow tJava component, output the text "No rows returned" (it's just an example), you can use other components to achieve your goals. thanks a lot.

[jensej]

Hi @Shicong Hong​ and @Aiming Chen​ 

So I tried to implement your suggestions in my job but it don't work as I want to.

Here is my job. I first Iterate different DB connections and if the DBinput fails (wrong hostname or something) then im printing that out in the csv. Next step is to check if the connection is ok but 0 rows are return I still want to print that out my csv with no rows returned. If connection is ok and rows are returned I am filling the csv with the data.

I made a test now and created a person named Talend in only one Database and the expected output should be that mostly no rows returned, maybe a couple of errors and only connection with rows returned.

Problem is that instead of no rows returned is stand error everywhere although all of this connections in the picture are working..

Can you see what Im doing wrong?

[tYrannoSaurusRex_1]

use SQLInput component with count(*) query.

For example, if your main query is as,

select column1, column2,.....

from tableA A inner join tableB B

on A.column1 = B.column1

where A.coluumn2 = ...

Make it,

select count(*)

from tableA A inner join tableB B

on A.column1 = B.column1

where A.coluumn2 = ...

Now this will give count. Make schema of SQLInput as row_count with Integer data type.

Connect this SQLInput to tJavaRow. Store that single count record in a context.

Use tJavaRow's IF connector, use it to check context value < 1 or context value > 0.

If context value < 1, then connect it to Email coomponent with appropriate message or else a tDie to stop the job.

[jensej]

I don't know the SQL. It's dynamic and can be written from other people so that's no option.