One Star

Do if-else based on row count

 

Hi,

 

I'm wondering, when I do a SQL query, how can I configure an different outcome based on the resulting row count?


In other words, return different status codes (using the tRESTresponse) based on the query outcome (rows returned vs. none).


Br,

Tom

 

  • ESB
2 REPLIES
Seven Stars TRF
Seven Stars

Re: Do if-else based on row count

Assume you are using SQL SERVER, you'll get the row count of the tMSSqlInput using the global variable tMSSqlInput_1.NB_LINE (just replace the component number according to jours). You can use this variable where you want using the following syntax: (Integer)globalVar.get("tMSSqlInput_1.NB_LINE")
Hope this helps.

TRF
Nine Stars

Re: Do if-else based on row count

NB_LINE may be not very effective if number of rows big - before return number of rows Talend will receive all data from server

 

I prefer use SQL construction tMSSQLRow, like

SELECT CASE WHEN EXISTS (SELECT id FROM t1) THEN 1 ELSE 0 END AS check_result

return 1/0 

 

or

SELECT CASE WHEN EXISTS (SELECT id FROM t1) THEN (SELECT count(*) as cnt FROM t1) ELSE 0 END AS cnt

return number of rows

 

-----------