Use Stop Heater with condition

Hey guys. I’m trying to run this def prior to the import def. Basically I’m filtering out records that do not contain any data in the “Account number” field. Then I do a lookup to see if the account exists in our WMS. Then I look at the length of the ID# returned from the lookup. If zero 0 I want to notify the user of the new account.

Everything works fine with the exception of the IF/Stop. The length is read properly and the IF is looking for 0 but I get the message even if the account does exist.

The IF logic is AND, and Condition =0, the Stop Heater is “with error message”.

Thanks in advance for any help, JD

Hi JD,

This behavior is related to the processing sequence of the Control (Yellow) Heaters. The Stop Heater are executed before the Filter Heater could filtered out not necessary rows!

In this case the Lookup Heater returns the value DBNULL for this account. DBNULL has also a length of 0 and the Stop Heater stops the processing with the defined error message.

You can solve this with a simple workaround. You’ll find an example in the attachment.

Note: If you are executing the attached example the Stop Heater never stops this definition. In case you’re removing the pipe/connection showing in the screenshot below the Stop Heater will stop the Definition.

Robert,

Thanks again for your help. Your explanation about the stop heater running prior to the filter made perfect sense. I was unable to get the desired results but I did find a workaround. Not sure if it’s the version or the lookup heater. Either way your assistance helps with production.

Stay safe

JD

Hi JD,

You’re welcome.

The Lookup Heater is indeed tricky and because this outdated. Try to use the SQL Heater instead. With the SQL Heater you can perform any SQL statements.

For example if you want to perform a data lookup you have to use the following SQL statement.

In this case you must set the FlowHeater Parameter $KEY-PARAMETER$’ with the Set Parameter Heater , that’s all.

You’ll find a lot of examples here in the forum: SQL Heater Examples

Thanks for the info Robert,

We do a lot of lookups during the imports I will make good use of this.

Always grateful, JD

Hey Robert,

I am using this more and more. But, how do I run this with two Key Parameters.
select FIELDNAME from T_TABLENAME where KEY-FIELD-NAME = ‘$KEY-PARAMETER$’

I tried it a few different was and was unsuccessful searching for a solution.
A simple line will be fine and is there anything unique about the data types in the structure?

I need to narrow the search by only looking at customer products for the SKU's PK.

Thanks, JD

Hi JD,

You can use as much parameters you need. You just have to set each parameter with the Set Parameter Heater before.

Here you`ll find some examples
select FIELDNAME from T_TABLENAME where KEY-FIELD-NAME in (‚$KEY1$‘, ‚$KEY2‘)
select FIELDNAME from T_TABLENAME where KEY-FIELD-NAME1 = '$KEY1$' and KEY-FIELD-NAME2 = '$KEY2$'

Note: The SQL Heater just return the value of the first available record!

After seeing your response I knew the problem had to be with data types.
And it was with the data types after the SQL Heater.

All is good up and run.

As always thanks for your time and help.

JD