A problem with Decode
I ran into an interesting behavior of decode today. The security team wanted a report to verify that data retention jobs were working properly. I wrote a query to find the oldest overall record, the oldest record still retaining the data the retention jobs would erase, and the counts of each. Well, the data looked pretty good at first glance and it seemed accurate when it highlighted a few instances where the retention jobs weren't scheduled correctly. However, one date kept surfacing over and over. I didn't think much of it, but the security team wanted an explanation. So, I dug in and they were right. The resurfacing date didn't match the data. My query wasn't correct. But why?
Well, first off, I'm a bit of a an old dog. I've been at this a while and I might be a tad stuck in my ways. Because of that, I have habits and one of those habits is relying on the decode function and eschewing the case function for most uses. This time it bit me. My decode looked something like this:
min(decode(retention_column, null, add_months(sysdate, 1), transaction_date))
So, if the retention column is null, use a dummy date, else consider the date of the transaction and once all that is done, get the earliest one. Seems relatively straightforward, right? Yet, the results didn't match the data. I just wasn't finding the issue so I called in a new set of eyes. He confirmed that I wasn't crazy and that it looked like it should work. He dismantled the statement a little more and tried some changes to find out what was going on. So, it turns out that the Decode statement automatically converts parameters to string values. Then the min was happening on the string, not the date. That is why dates of the first were coming before the real minimum date. Problem solved. So, what I had to do is convert the results of the decode function to date and then find the minimum. It looks like this:
min(to_date(decode(retention_column, null, add_months(sysdate, 1), transaction_date), 'dd-mon-yyyy')
A problem with people
So, I really just need to vent about something. There was a bit of a fiasco at work a couple weeks ago. Someone was tasked with clearing a test database and ended up being in the wrong one when they ran their scripts. I found out about it a few days after it happened and I was tasked with adding a few users in so that they could get in the system and do what they needed to do. Someone also mentioned that the latest backup was 6 months old. It only took me a couple of minutes to add in the users.
I took some time and figured out a way to pick a random number of customers, a random number of days and a random number of transactions for those days. I was able to generate 6 months worth of test transactions and effectively replace the data that had been lost. I've been super excited about this and telling everyone that I run into. However, I haven't heard jack shit from the people directly involved with this system. They couldn't give a shit less about my breakthrough.
Now, there is a general malaise of apathy that has infected probably 90% of the people at my work. Maybe that is a little harsh. It is probably more like 60% apathy and 50% incompetence. A few of you math wizards out there might have figured out that that covers more than the sum total of people. And I say nay. Those aren't mutually exclusive. There's 15 to 20% of people that both don't know shit and don't give a fuck about it.
It is difficult to keep a head of steam up when beset with the lazy and the stupid on almost all sides. Probably more than half of the few competent people that are left have one foot out the door. The others are bravely trying to stem the tide of meh, with varying success. I just don't get how people do not see the potential in the randomized data generator I created. In my mind's eye, I picture a system that can user docker and puppet to spin up a new test instance, kick off my script and simulate half a year's worth of activity all in a matter of moments. So, you are showing the application off to a new set of customers? How about a personalized demo in a personalized test environment spun up at a moment's notice? Maybe I should lay off the paint thinner. Or maybe I'm onto something here. Hell, I've had several of the brighter stars at work singing the praises of docker and puppet for months now. One of my illustrious colleagues just accepted an offer to go work for PuppetLabs in Portland. In a world where a foul-mouthed, halfwit database developer can see massive potential in this stuff, why can't everyone?
Alright, I get it, my feelings got hurt. For a guy who looks like I look: Sons of Anarchy meets Trailer Park Boys meets Cops meets Lizard Lick Towing, I can be rather sensitive. But for realz, maybe try giving a shit. Let's just start with one shit given per week. Baby steps, alright? Rome wasn't built in a day. Or perhaps I'm just pissy because my work wasn't appreciated enough. Perhaps I'm being too hard on everyone and I'm lashing out in anger and painting with too wide and negative of a brush. Perhaps everyone is so in awe of my randomizer that they've been struck dumb and are screaming praise silently in their own heads. Or perhaps not. Perhaps, the "meh, good enough" crowd really are fucking it up for the people that are on the streets.
Either way, I feel properly vented. I'm going to bed. See you all next time. In the meantime, be careful when using the Decode function and be extra careful when pissing excellence. The mediocre and unenlightened might just treat it as regular piss. Toodles.
