20 Jan

< NULL

This is from my Cyberquery days and it involves a dead inventory report. The idea was to produce a report that tracked inventory that had not been sold in the last 365 days. The system tracked the last_sale date in the sku file so I’m sure it looked easy to do. Here’s the Cyberquery code,

WHERE
last_sale < todaysdate – 365

I wound up working on the report because they wanted to change the date to 270 days. So, I changed the code to 270, looked at it for a bit, and did a “wait a second”. What happens if we never sold the item?

Well, since a NULL value can’t be less than anytihng, if the item never sold it would never show up on the report. The solution was actually pretty easy in this case because we also had a last_receipt field so all we had to do was modify the code to look something like

WHERE
last_sale < todaysdate – 270
OR (last_sale = NULL and last_receipt < todaysdate – 270)

Note: it’s been a long time since I’ve written CQ code but according to a sample that I still have that is how it tests for NULL values.

Anyways, there are two morals to this story. The first moral is be aware of NULL values in your data. They bite!

The second moral? There isn’t one. The guy who wrote the report wound up a VP in the company.

Leave a Reply

Your email address will not be published. Required fields are marked *