Fun with SQL

My group held it's first Peer Code Review today. It was my code (the code I've screwed up a couple of times). One of the requirements (I stuck to them this time, yeah for me!) was to limit the data we were pulling from another system to only the last 48 months. There were three different procedures that needed this date but I didn't want to break the existing automated job so I defaulted everything to:

p_date_from DATE DEFAULT ADD_MONTHS( TO_DATE( TO_CHAR( SYSDATE, 'YYYYMM' ), 'YYYYMM' ), -48 )

I wanted the last full 48 months and this worked just fine.

This is a fairly long process which takes anywhere from 8 to 12 hours to run, depending on system resources. One of my colleagues asked what would happen in the event of a crash (on the last day of the month) and the required re-start (on the first day of the month). One set of data would have everything going back 49 months and the other 48 months which would create a lot of errors. A great catch.

Since we're in maintenance mode now (re-architecting the whole thing), we decided not to stop the deployment, we looked at the calendar to see when the first occurence was in which the 1st fell on a Sunday (the job is scheduled weekly on Sunday)...which now that I think about it, was probably wrong. We should have been looking for a Sunday in which was also the last day of the month. Anyway...

I like trying to answer questions with SQL. So I wanted to find the occurences on which Sunday was also the first day of the month (despite while writing this realizing that we should have been looking for the last day, again, digression). Here's my resultant query:

SELECT
  ADD_MONTHS( TO_DATE( TO_CHAR( SYSDATE, 'YYYYMM' ), 'YYYYMM' ), myrownum )first_day_on_sunday
FROM
  ( 
    SELECT rownum myrownum
    FROM dual
    CONNECT BY LEVEL < 100
  )
WHERE TO_CHAR( ADD_MONTHS( TO_DATE( TO_CHAR( SYSDATE, 'YYYYMM' ), 'YYYYMM' ), myrownum ), 'D' ) = 1
  AND TO_CHAR( ADD_MONTHS( TO_DATE( TO_CHAR( SYSDATE, 'YYYYMM' ), 'YYYYMM' ), myrownum ), 'DD' ) = 1
/

FIRST_DAY_
----------
06/01/2008
02/01/2009
03/01/2009
11/01/2009
08/01/2010
05/01/2011
01/01/2012
04/01/2012
07/01/2012
09/01/2013
12/01/2013
06/01/2014
02/01/2015
03/01/2015
11/01/2015

I'm not so sure it impressed my colleagues, but it certainly reinforced the notion that I'm a nerd.

Humor in the Workplace

In an effort to deal with my recent screwup (one of two recent ones), I decided to try and laugh at it a bit.

Our organization is very young and since the on-boarding of our new CIO, our directive has been to stabilize current processes. In that regard, the VP who heads up the Infrastructure team has placed placards on his office window signifying the number of days one or another system has been up with no interruption in service.

I ran into him in the hallway and suggested he put one up for me too, "Days since Chet 'messed' up Production code." He got a hearty laugh out of that.

I've always been able to laugh at myself, if not immediately, then soon after. So I put up a hand made sign in my cube that read the same thing; I'm at 21 days. Hopefully it will remind me as I'm pouring through code not to touch that which is out of scope with the current requirements. That was my mistake on both occurences.

It's definitely a talking piece and hopefully people can laugh at it (as I can...sortof), but it will be a constant reminder to me as well.

Why I Blog

I never really had a true mentor, well, a physical one anyway. I could ask questions to my first boss, but it was apparent rather quickly that they better be good ones.

So I took to reading AskTom everyday. Mostly the new posts at first but as time went on, there were fewer and fewer of those. Tom was mostly answering old questions. But they were fun to read and you could really get insight to his thinking. That's what I needed the most. The Why of doing things. I think this post illustrates more the Why, I still read that one from time to time.

Then a couple of years ago he began blogging. That really gave me a view into his mindset. I learned about instrumentation there, how to ask questions, how to answer them and the rule of thumb, to name but a few.

I think that was the first blog I read consistently.

I found his advice and his technical expertise inspiring. More than anything though, I liked have a view into his world. I believe that alone helped me get over the technical hurdle, to begin to really understand what was going on and why I should do things a particular way.

I hope I can do the same. I want to share my experiences, challenges and mistakes so that others may learn from them. I also want to provide more technical content, even though much of it is answered in one place or another, just because I have a different take. Someone might relate to my style and find me inspiring (that would be way cool).

I now consume about 40 or 50 different blogs daily. Most of them are Oracle related, but I also read many business blogs, opinion blogs, security blogs and data warehousing blogs (though I haven't found one yet that really inspires).

I believe it is a good thing to be well rounded and I try to be.

So that's why I blog. I want to share. Hopefully you'll find something you can relate to here.

Good Day to Worse Day

Today I got the opportunity to have lunch with my CIO.

A few weeks ago I sent him a manifesto, which I would now classify as more of a philosophy. He kept promising me he would articulate a response via email. Being the CIO of a Fortune 400 company, I figured he had better things to do than to write out an email to me. So I offered him up a trade, lunch in exchange for the email. Surprisingly, he agreed.

Today was the day but I fully expected him to cancel; surely something else would come up. Nothing did, but he did move it back by 15 minutes.

Down to the cafeteria we went (I was really hoping to go out to lunch, just to get a ride in his Porsche Gemballa). We sat in a booth and started talking. We discussed everything from my group (excellent group of people, talented and fun), using MySQL databases for one-off projects (I was for putting them in a single Oracle database), to the state of our current OLTP application (crud).

He mentioned user-defined fields (OLTP) and I told him about one instance where someone in our company created those in an internal application. I didn't say anything at the time because it was not in my group and I didn't want to call someone out for something I thought was wrong. He told me I should have, that it would be in the best interests of the company. Then he said something that I have heard him say in our All-Hands meeting, "Let me be the one that makes the wrong decision."

That sealed the deal for me. I have liked him and what he has done since he got here, but that one comment told me that he took his job as leader seriously. I was thoroughly impressed.

So that was the good (great) part of the day. I felt great because our CIO listened to me prattle on for an hour and listened to me. He even used one of my analogies (well, not mine really) in his management meeting a short time later.

Now on to the worse part.

The application I have been working on for the last few months required an Emergency Fix (EFIX) because they had detected a bug. I realized quickly that I was the culprit. Something that had worked previously was changed by me in an effort to re-factor the code. It wasn't broken. There was no specific business requirement to re-factor it, I just did it...and screwed it up. The ironic part is I had just sent out an article to my co-workers about discipline making good developers.

I told this to the Business folks, what I had done; and apparently I hadn't earned any brownie points with them because they escalated it to my boss and VP. Which of course had to go to the CIO as well...

I definitely screwed up. There's no way around that. I know better than that. Oh well. It was certainly a lesson in humility. I'm just thankful I still have a job...