topic Null Handling from a query result in Talend Studio

Null Handling from a query result

Jennifer_siders — Sat, 16 Nov 2024 02:04:05 GMT

I have a simple job

Query Q1 - if the query returns records,then it will take Path1

- If the query doesnt return any record, then take path 2

From my Query Q1, i get a Id and i have built an expression in the tmap using the id as below. But though th query returns null or no records, still the flow doesn't take 'Record Doesnt exist' route.. ami missing something?

Re: Null Handling from a query result

TRF — Wed, 24 Jun 2020 13:31:29 GMT

It seems there is a syntax error on the expression "row5.id. == null" should be "row5.id == null" (no point after id).
If Q1 returns no result, it's normal to not pass by the "Record Doesnt exist route" (no record, no dataflow).
You will go there only for records where id is null (not empty if it's a string).

Re: Null Handling from a query result

Jennifer_siders — Wed, 24 Jun 2020 13:35:58 GMT

I have updated my screenshot, but still same result..

I have changed it to row5.Id == (" ") as Id is a Salesforce Record Id still no luck

Re: Null Handling from a query result

Jennifer_siders — Wed, 24 Jun 2020 13:38:04 GMT

Oh ok, @TRF understood that if there are no records, then it will never take 'Record Doesnt Exist' path? if so how do we control the flow from a tsalesforceinput component in cases where there is no data returned?

Re: Null Handling from a query result

TRF — Wed, 24 Jun 2020 13:45:20 GMT

Well, it depends of what you try to do.
Sometimes you may just do nothing.
Sometimes, you may store the query result to an intermediary object such as a file or tHashOutput then control the number of records stored in this object. Remember you may also retrieve the number of records return by the query using associated global variable such as "tSalesforceInput_1_NB_LINE" which is an integer.

Re: Null Handling from a query result

TRF — Wed, 24 Jun 2020 13:55:13 GMT

Also careful, you should NEVER use == to compare string variables. Instead use equals() method like this "".equals(row5.Id)

Re: Null Handling from a query result

Jennifer_siders — Thu, 25 Jun 2020 05:55:29 GMT

Hi @TRF

I think i am close to the solution. I have utilised NB_LINE.

As shown below, the NB_LINE value is 1 if there is a record and 0 if no records are found. how can i use that value tSalesforceInput_4.NB_LINE = 0 to change the path to Tmap_6->tSalesforceoutputbulkexec_2

Re: Null Handling from a query result

Dave5 — Mon, 27 Jun 2022 18:12:34 GMT

Hey @Jennifer Siders

I'm having a similar issue (which I posted here). I'm not sure what you did in this picture but I'm wondering if you might have a moment to help me do a similar flow. Like you, I want a specific flow when a query returns no values.