Tuesday, January 25, 2011

Debugging SQL script caused by data error or missing

Short version:
1. Check which table that cause the data missing by comment all table in the script and re-enable the table one by one, along with the table's filter (where clause).
2. After the table has been found where the data start missing, then only narrow down which filter that caused the data missing.

Long version:

I got a bug repot from analyst and it goes something like below.

"The report does not shows any result although it just recently changed on the fields. No logic or filter has changed so far. Please investigate."

When I open the SQL script, my jaw dropped because it was a very long script. I lost my count only how many line were there. There are 2 SQL union all together and thus it's not something easy to debug.

The dumb way: I tried to comment the filter one by one and see which one make the result appear back again. Then I realize that this approach is very time consuming. Each time execute the script to check the result, it will take a long time due to large table.

The smart way: I changed my approach by going after the table. Comment all the table and re-enable them one by one. Once result start to appear/missing after table is disable/enable (along with its filter) will shows most probably the problem lies on the filter for this table. Well after couple of tries, finally I found what caused the data to be missing from the report (It's a function that used for that specific table field and somehow the data does not match).

In the end, one more support ticket has been solved. :P