Bizoneness: When SAP B1 Should Crash

While this summer has been a source of chaos for me, I've wanted to return to regular writing here. I haven't gotten a topic that was suitable to write about lately until a recent incident brought up a topic that I do have a controversial take on: Let SAP B1 crash.?

Many of you will never deal with this, but as you get into the world of add-ons and external files, you'll find that this becomes more of an issue. My shipping add-on was the culprit in my case, but it dragged SQL and my dashboard add-on into the mess.?

My shipping add-on has a table I'll call SHIP to preserve the vendor's identity. SHIP receives data from online shipping systems, such as the cost of shipping and the tracking number. I use SHIP in all kinds of reports and dashboards in SAP B1 to tell the shipping status and tracking numbers.?

One afternoon recently, tickets came in about errors people were suddenly getting in SAP. While multiple modules and dashboards were involved, all had the same message:?

Conversion failed when converting the varchar 
value 'SAMPLE' to data type int        

The error message from a sales order mentioned SHIP. I checked the data with a simple?

SELECT * FROM SHIP         

Unfortunately, that was a mess. There was no good way to sort the columns. SHIP has all of its columns as?varChar?strings, and dates, in particular, come in multiple formats. So I tried again, this time casting the date

 SELECT CAST(ShipDate as DATE) as ShippingDate ,* FROM SHIP         

Using double-clicking on the columns, I ordered the data by date and found that errant row.

This package was manually entered into FedEx, and the shipping person put SAMPLE for the DocNum. DocNum is a varChar in SHIP, so neither Fedex nor my shipping module complained about that string in a number. Even the query above didn't complain. The problem was here:?

Select
    T1.DocNum,
    T0.CAST(ShipDate as date),
    T0.TrackingNum 
FROM 
  SHIP T0 
  INNER JOIN ORDR T1 ON t1.Docnum = t0.docnum        

Parameters and joins from ORDR would compare the integer value for DocNum, against the varchar SHIP.DocNum. Usually, implicit casting handles this. SQL assumes the string is supposed to be a number and automatically casts it correctly. But when the SHIP.DocNum is SAMPLE, then casting fails, and we get the error we saw above.?

Through the shipper online tools, we were able to fix the data, but it brought about a question: is there a fix for this when there is bad data -- Should I do that?

The fix is to use TRY_CAST to force cast SHIP.docnum to an integer. One version would be this?

  WITH Shipping as (
   SELECT 
      TRY_CAST(T0.Docnum) AS DocNum,
      T0.CAST(ShipDate as date) AS ShippingDate,
      T0.TrackingNum AS TrackingNumber
   FROM SHIP T0 
  )
 
 Select 
    T1.DocNum,
    T0.ShipDate,
    T0.TrackingNumber 
FROM 
  Shipping T0 
  INNER JOIN ORDR T1 ON t1.Docnum = t0.docnum        

I do all my Casting in the WITH so it is easier to use in the main SELECT. Unlike CAST, which would cause an error if unable to cast, TRY_CAST will successfully cast the number or return NULL, preventing the system from crashing.?

And this gets to the core question: should you use TRY_CAST and prevent crashes? I want it to crash, especially if there is no way to indicate a problem. Within SAP B1, there are ways of handling the situation by preventing bad data from getting into the system. I can add validation routines using a product like Boyum B1 Usability Package. However, external data like the shipping data here, EDI transmissions, and E-commerce portals is not so easy since you do not have control of the input. Hard fails often get you to the root of the problem a lot faster. Preventing failures may produce more problems.

For example, SHIP.DocNum is a primary key. If the value is NULL, that means this shipment is not tied to any sales order. If not tied to a sales order, it is not tied to a delivery document or invoice, and inventory is not allocated properly, nor will shipping charges show properly as an expense. Indeed as far as SAP and our Shipping add-on are concerned, the shipment disappears. This could happen multiple times, and no one knows unless staff wonder why a particular order is missing from invoices.?

If there's a problem once with a hard fail scenario, everybody knows it. For IT, it becomes easier to track down the culprit, but there is a Social aspect to this too. Since SAP B1 implementations are usually small businesses, everyone knows all the other users. Thus, every user knows who screwed up their day. Social pressure keeps users from making the same mistake twice.?

Some errors can be handled. Sometimes there is enough information to fill in the problem. For example, suppose SHIP returns the delivery date as a varChar. I could cast that to a date, and if it is corrupt, use the sales order's date, which happens to be a dateTime.?

 CASE WHEN TRY_CAST(t0.DueDate as DATE) IS NULL
     THEN t1.dueDate
     ELSE CAST(CAST(t0.DueDate as DATE) as DATETIME)
 END as DueDate        

I use TRY_CAST to determine if the cast will work and replace the data. You can't do this for a key, but you might get away with it with some other data.?

When handling errors, especially data errors, be careful how you do them. Not every error should be smoothed over just to make everything look nice. Instead, you want to find root causes and fix the errors so you don't see any crashes. To do that, you want to know about some of them either as symptoms of a bigger problem or as the problem itself.