Null in number type DE field not treated same in direct reference, it returns "0"
Trailblazer Community

Null in number type DE field not treated same in direct reference, it returns "0"

Marketing Cloud Email Studio

Last updated 2020-02-11 ·Reference W-7186736 ·Reported By 0 users

In Review

Summary
When referencing a number type field in a data extension via Lookup(), if the value of the field is NULL the value returned from the lookup function is NULL.

On the other hand, when referencing a number type field in a data extension directly (Send to a DE), if the value of the field in the DE is NULL the value is represented as 0 in AMPscript.

Repro
1) Create a sendable DE with a nullable number field.
2) Inject a record into the DE, but don't assign a value to the number field.
3) Create an email referencing the DE
a) directly (ex: SET @foo = numberFieldInMyDE) and
b) via Lookup (ex: SET @bar = Lookup('MyTestDE','numberFieldInMyDE','SubscriberKey','somesubscriberkey')
4) Print the values of @foo and @bar
5) Do a send preview or test send to the DE and see the difference between the two

Workaround
Use lookup function for nullable number fields.

Reported By (0)

Any unreleased services, features, statuses, or dates referenced in this or other public statements are not currently available and may not be delivered on time or at all. Customers who purchase our services should make their purchase decisions based upon features that are currently available.