Shutterstock

Imagine filling in for someone on maternity leave. Would you simply do your job as you were instructed, or would you try to improve the processes currently in place?

In this story, one tech support worker is in this situation, and he decides to make confusing processes less confusing. In the process, he also discovers a huge problem with the data on one of the client reports. He knows he needs to tell his boss, but then they have to decide if they should tell the client.

Keep reading for all the details and to see how the story plays out.

Need approval from several levels of management to change a script that gave incorrect results

I got a job in operational support back filling for a person going on maternity leave.

There was a two week hand over, first week was overwhelming, second week she did not come in, I was thrown in the deep end.

When I started I knew little to no SQL, luckily it was only part of the job.

This sounds confusing.

I was given some scripts that had to be run every 28 days for KPI reports.

They were all over the place, some ran automatically, others you had to put in the start and end dates of the KPI manually.

Some of the manual reports you had to put in the Sunday of the KPI, some you had to put in the Monday after the KPI period, basically a mess.

OP tried to make it less confusing.

First thing I did was to standardise the manual ones that they all used the TRUNC of the Monday (the midnight between Sunday and Monday).

Next I looked at how the automatic scripts were calculating the start and end dates.

They used an anchor date, calculated the MOD between then and SYSDATE. That was then used to calculate the end date of the current KPI period.

If you ran the script 2 days after the end date the MOD was 2, ran it 5 day then the MOD was 5.

It was going well.

After a couple of months I was getting to know SQL quite well.

I convinced the DBA to schedule the scripts so I did not have to run them myself. Except for 1 of the scripts that took 6-7 hours.

I would kick it off before I left work and the next day the results table was populated….until I was getting ‘snapshot too old’ errors.

OP understood why the error was happening.

When you start a query on an Oracle database it remembers the time you start.

While your script is running data will change, however it will use the transaction logs to work out what the data was when your script started.

The error I was getting is because there is a limit to how much it can keep in the transaction logs.

OP also knew how to fix the error.

Before I started getting this error I had been learning to use WITH clauses in my scripts.

I set about refactoring this script using WITH clauses.

While doing the development I would limit the script to a subset of items. I would compare my script against theirs to ensure I was getting the same results.

But there was a bigger problem.

About a week into the process I started getting different results in my script then the one I inherited.

Using WITH clauses allows you to build up a VERY complex query one step at a time ensuring each step is correct.

I checked and double checked my script but could not find where it was going wrong.

I ended up limiting the script to one particular item, at every step it gave me the correct results. I then limited the original script to the same item. It gave results that in no way reflected reality, their script was incorrect and had been for at least 4 years.

Let’s see how the boss responds.

I bought this to the attention of my boss. He was the one that had someone at the parent company to create the script and signed off on it.

The person that created the script had a degree and been doing Oracle for many years.

I demonstrated that their script gave incorrect results on this item and how mine gave the correct answer.

Since this script was used for so long and that we had been providing the incorrect data for all that time it was debated whether we should keep the error or not.

There were several problems with that.

There really were a lot of problems.

First is a moral one, don’t lie to your customer that pays you millions each year to run the system.

Second if they found out that you knew the KPI data was wrong and continued to give them wrong data it would damage trust.

Third the item in question was a situation that was not the norm but it was not unique.

Fourth I had no way to figure out how to count the same situation the same way as the original script.

Fifth the original script was no longer viable. While I was developing the new script I had been trying to run the original every day, even weekend’s hoping the lower number of transactions would allow it to finish.

They decided to tell the client.

I had several meetings with the author of the original script to explain how mine worked.

After they signed off on it I had several meetings with multiple levels of management in my company explaining the situation. I ran my script against the previous KPI period to show the difference in the totals.

In the end there was a meeting with the customer, I ended up explaining to them the situation. What situation caused items to be counted incorrectly, demonstrated the difference in the results and why we could not continue to use the original script.

They then had to have their own meetings and sign offs on the changes.

All up it was almost 6 weeks after the end of the KPI period that we ran my new script for that period as well as the current period. I was also able to get the DBA to schedule it because my new script only took 45 minutes.

Just think, if OP had never covered for the person who went on maternity leave, it may have been possible that this error never would’ve been caught.

Let’s see how Reddit responded.

A helpdesk worker shares their frustration.

This is a good question.

Here’s an argument for not fixing known errors.

That’s a lot of data!

It sounds like OP did an amazing job finding that error and learning the job on the job. He deserves a raise!

I hope the client was understanding. These things happen.