Calculate Total Possible Score for Wii Bowling, Power Throws Needless to say I have to much time on my hands. I've become obsessed with two Wii games, Bowling and Tanks. I play over and over. I had a perfect game in bowling the other day which wasn't quite as exciting as I thought it would be.

I've also been playing the "Training" game of Power Throws (bowling). Your given 10 shots with another row added each frame. You start out with 10, then 15, then 21, etc. If you get all the pins, you get a bonue (total pins * 2). So far, my highest score has been 651.

I've never bothered to figure out what the total possible score is though.

SQL to the rescue.

Using analytics and the CONNECT BY LEVEL option (10g and higher), it's fairly easy:

SELECT
  pin_row,
  pins,
  running_total,
  rt_with_bonus
FROM
  (
    SELECT
      rownum pin_row,
      rownum pins,
      SUM( rownum ) OVER ( ORDER BY rownum ) running_total,
      ( SUM( rownum ) OVER ( ORDER BY rownum ) * 2 ) rt_with_bonus
    FROM dual
      CONNECT BY LEVEL <= 13
  )
WHERE pins >= 4

I had to set the filter on the outer query because you start with 4 rows (10 pins). And the results:

CJUSTICE@XE>BREAK ON REPORT
CJUSTICE@XE>COMPUTE SUM OF RUNNING_TOTAL ON REPOR
CJUSTICE@XE>COMPUTE SUM OF RT_WITH_BONUS ON REPOR
CJUSTICE@XE>/

   PIN_ROW       PINS RUNNING_TOTAL RT_WITH_BONUS
---------- ---------- ------------- -------------
         4          4            10            20
         5          5            15            30
         6          6            21            42
         7          7            28            56
         8          8            36            72
         9          9            45            90
        10         10            55           110
        11         11            66           132
        12         12            78           156
        13         13            91           182
                      ------------- -------------
sum                             445           890

Voila! 890 is the best possible score if I strike every frame. I did manage to pass my high score by 9 pins tonight as well. It's a great day!

So how come no one wants to hire a guy that can do this kind of fun stuff with SQL? ;)

Labels: funny, puzzle, sql

  ¶ 8/20/2008 10:04:00 PM 3 Comments Links to this post

  iPhone AppStore oops? My wife has an iPhone (I have the $10 phone I got with my 2 year plan). I love the thing. I steal it whenever I can.

One of my favorite things to do though is to ask her a random question, you know, one that would take 3 seconds to google and find the answer, just to test her. Usually, she just says "I don't know"

Um...you have an iPhone...and you can look it up right? (lots of sarcasm)

Oh yeah.

I probably do it because I have the $10 phone and I'm jealous.

Anyway, she showed me a screen shot yesterday of an app in the AppStore that was selling for $999.99. Funny. What do you get? A pretty picture. What else? Nothing.

So, via Jake then Digg, I found the following picture:

Labels: funny, iphone

  ¶ 8/07/2008 10:17:00 PM 1 Comments Links to this post

  I Found a New Job! http://www.foxnews.com/story/0,2933,395181,00.html

My wife thinks I'm perfect for it!

Labels: funny, work

  ¶ 7/31/2008 11:27:00 AM 4 Comments Links to this post

  Don't Go Into the Office Whenever my boss asks me to come with him...I will politely tell him I'm not going.

I was laid off yesterday.

I want to see how many jobs I can have in one years time apparently. I didn't undermine myself this time though.

So if you know of anything in the Tampa Bay area, please let me know.

Labels: funny, work

  ¶ 7/29/2008 02:34:00 PM 11 Comments Links to this post

  Men Are From Venus? So my wife calls me today to share a funny story.

Apparently my son (Little Chet) asked about metorites. He had seen them on Jimmy Neutron - Boy Genius. She starts explaining what they are and the conversation led to talk about the space shuttle and the space station.

LC: What do they do up there?

WIFE: They do all kinds of stuff, experiments, look at planets, etc.

LC: The look at planets? Like Planet Penis?

WIFE: What?!

LC: Planet Penis, the one close to Earth.

WIFE: You mean Venus?

LC: Yeah, that's the one.

He doesn't get "Uranus" yet...

Labels: funny, LC

  ¶ 7/10/2008 09:50:00 PM 2 Comments Links to this post

  Count The K's I have this really annoying co-worker who happens to be the DBA. Everytime he walks by my desk he pounces on my keyboard. I've learned to Windows Key + L to lock the computer when I hear him approaching, but occasionally I forget.

As I'm standing near my unlocked computer he starts typing furiously in my sql*plus session (shouldn't he know better?). I should know better...

Walking away he asks, "How many K's are in there?"

I ignored him, but then wondered myself...what's the best solution to this problem?

So, here it goes:

DECLARE
  l_count_k NUMBER := 0;
  l_string VARCHAR2(300);
  l_string_length INTEGER;
BEGIN
  l_string := 'W34 6OKWE;KTL;SERT;LKSERTSLKRLTKRTKL;KERTL;ERKT;LKERLTKERKT;EKT;LEKRT;LEK;
LTKERTKERKT;LEKRT;LEKRT;LER;LTKETK;ERKT;LEKRT;LEKT;LEKR;LTKETKERTKL;ERKTL;ERKTL;ET;LEKT;LEKRT;LERTL;
EKRL;TKELTERTKL;ETL;EKTL;EKTL;ETL;ERTL;EL;TERTRTL;ERTL;ERTL;ERTL;EKRT;LEKTL;EKT;LERTL;EKL;KER;LTKE;L
TKELTKER';

  l_string_length := LENGTH( l_string );

  FOR i IN 1..l_string_length LOOP
    IF SUBSTR( l_string, i, 1 ) IN ( 'K', 'k' ) THEN
      l_count_k := l_count_k + 1;
    END IF;
  END LOOP;
  
  dbms_output.put_line( 'Kk Count:  ' || l_count_k );
END;
/

Easy enough, 45.

Then I started thinkinhg...can I do this in pure SQL? Of course!

SELECT 
  SUM( CASE
         WHEN SUBSTR( UPPER( mystring ), rownum, 1 ) = 'K' THEN
           1
       END ) k
FROM
  dual,
  (
    SELECT 'W34 6OKWE;KTL;SERT;LKSERTSLKRLTKRTKL;KERTL;ERKT;LKERLTKERKT;EKT;LEKRT;LEK;
    LTKERTKERKT;LEKRT;LEKRT;LER;LTKETK;ERKT;LEKRT;LEKT;LEKR;LTKETKERTKL;ERKTL;ERKTL;ET;LEKT;LEKRT;LERTL;
    EKRL;TKELTERTKL;ETL;EKTL;EKTL;ETL;ERTL;EL;TERTRTL;ERTL;ERTL;ERTL;EKRT;LEKTL;EKT;LERTL;EKL;KER;LTKE;L
    TKELTKER' mystring
    FROM dual
  ) c
CONNECT BY LEVEL <= LENGTH( mystring )

COUNT_OF_K
----------
        45

1 row selected.

I'm sure many of you can do better than that. So let's see 'em. Prodlife, this isn't a complicators test either. ;)

Labels: funny, puzzle, sql

  ¶ 7/08/2008 09:00:00 PM 9 Comments Links to this post

  Funny License Plate

H20UUP2

First to figure out wins absolutely nothing but praise!

Labels: funny, puzzle

  ¶ 7/02/2008 08:33:00 PM 2 Comments Links to this post

  Failed Deployment... 236 Days
20 Hours
48 Minutes
30 Seconds

I hate making mistakes but I've made another one. My streak ends almost 237 days from my previous one.

Something so silly too.

In our source system, data was been double loaded somehow. So we decided on a surgical delete. A total of 7 DELETE statements needed to be run; 4 on the source system and 3 on the target system.

The source system went off without a hitch. I babysat the re-load of the source tables and was ready to have our load jobs run in our target system.

What's this? It ran in half the time?! How's that possible?

I pulled up our logs to find that zero rows were loaded into one of our tables. There should have been 45 Million plus.

I started to run down the possible causes:
1. Did the job we have in the scheduler that TRUNCATEs our persistent staging tables run? Nope.
2. Did I fail to instruct the DBAs correctly in the critical CR? Nope. Instructions look good.
3. Next to the logs, it ran fine on Saturday morning but not Sunday morning. What happened yesterday?
4. Ah yes, my CR. Open up the script...nothing out of the ordinary...and then I saw it.

On our target system, we use work tables to pre-generate a keys. It makes things a heck of a lot faster and removes the need for PL/SQL lookups in SQL (no, we don't have incremental builds yet).

So the work table needs to be DELETEd from first based on the keys from the first:

DELETE FROM some_key_table a
WHERE EXISTS ( SELECT NULL
               FROM the_main_table
               WHERE business IN ( 'TTT', 'TTR' )
                 AND dateof = TO_DATE( '24-MAR-08', 'DD-MON-YY' )
                 AND my_key = a.my_key );

OK, no funny business there.

Then I DELETE from the main table:

DELETE FROM the_main_table a
WHERE EXISTS ( SELECT NULL
               FROM the_main_table
               WHERE business IN ( 'TTT', 'TTR' )
                 AND dateof = TO_DATE( '24-MAR-08', 'DD-MON-YY' ) );

As I look at it I wonder WTH I was thinking using an EXISTS clause on the main table. That's the source.

But do you see what I missed?

See it yet?

OK, I left out the "AND my_key = a.my_key" from the inner query. Obviously a stupid approach, but it would have worked. The best way to do it is to just get rid of the EXISTS clause:

DELETE FROM the_main_table a
WHERE business IN ( 'TTT', 'TTR' )
  AND dateof = TO_DATE( '24-MAR-08', 'DD-MON-YY' ) );

Live and learn, live and learn...

Labels: funny, humility, sql, work

  ¶ 3/30/2008 10:21:00 PM 0 Comments Links to this post

  Bowling for IT Another non-Oracle related post. Just fun at work.

Last Friday we had another one of our IT all-hands meetings. My goal at each one is to make either the rumor list (of the top ten variety) or to be somehow be involved (hopefully good) in other ways.

Two months ago I was promoted to SVP of IT, because I was able to talk my CIO into it. Last month, I was promoted to CEO because I happen to resemble our new CEO. This month, the "light" piece was a "Where are they now?" complete with old/new pictures of IT employees. (Needless to say I am an only child...I crave attention!)

To say I've gained weight since starting a career/marriage/family would be an understatement. I went from a lean and mean 170 to about 250 now. The first pic I had just completed a sprint triathlon in Clermont, Florida. The second was sometime after the birth of my first child.


I take about every opportunity I can to send out the first one to new friends. "I didn't always look like a slob. That got to be my "before" picture and my "after" picture was my mugshot from my ID badge (yikes).

There I was 10 feet tall and looking great! I no longer had to send the picture out to anyone (and risk possible graffiti, though I guess posting it here doesn't help matters).

The important part was that I made all-hands again. I think that's 12 in a row.

And finally to the title of the post. After our all-hands meeting we went bowling. Food and bowling were free. I did notice however that there are quite a few, um..., drinkers among IT. I would certainly say that I fall into that category. At one point, it took so long to get a drink (only one bartender for 100 some odd people) I tried to ban the sale of mixed drinks so it would speed things up. However, my ploy didn't work.

I just bought two beers and waited for everyone else to follow-up with pitchers!

Gotta have fun at work right?

Labels: funny, work

  ¶ 3/04/2008 09:08:00 PM 0 Comments Links to this post

  Application Developers vs Database Developers It started innocently enough with this article. I sent it out to about 20 colleagues.

The best line from the article:
"Jerry: "Yeah, databases cause lots of headaches. They crash all the time, corrupt data, etc. Using text files is better."

One of my more recently arrived colleagues (I'll call him Mr. M) replied to everyone with this statement:

"Kind of funny actually, databases are less and less important at the large investment banks, where they basically load everything up into a data grid across a several hundred node cluster. Writing to the db is way too slow."

This started a day long exchange of emails. What follows is the entire thread (up until my last post tonight).

Me:
"I would just argue that they don’t necessarily know how to write to databases. I would however love to see benchmarking done on both methods. Would be an interesting test..."

Mr. M:
"Well, my understanding is they just can’t scale out the db enough. Even something like Oracle RAC won’t work. And outside of the military, these are probably the top 1% of programmers in the world building this stuff."

Me:
"A benchmark would be the only way I would believe it.

If you said the top 1% of database developers tried it and failed, I would be more likely to agree.

My experience is that application developers != database developers. Different type of thinking involved."

Mr. M:
"'A benchmark would be the only way I would believe it.'

Do you need a benchmark before you would believe in-memory retrieval is faster than disk retrieval? Essentially, this is what we’re talking about.

'If you said the top 1% of database developers tried it and failed, I would be more likely to agree. My experience is that application developers != database developers. Different type of thinking involved.'

Why? It’s an issue to do with application performance not simply database performance. Database concerns are a subset of application concerns, essentially a specialization, requiring less encompassing knowledge. ;)

From the article you linked to (http://www.watersonline.com/public/showPage.html?page=432587)

"Better data management is the answer, says Lewis Foti, manager of high-performance computing and grid at The Royal Bank of Scotland (RBS) global banking and markets. "For very large compute arrays, the key issue is data starvation and saturation. This problem requires data grids with high bandwidth and scalable, parallel access,
...
Banks are learning that data management in a distributed grid environment is very different from online transaction processing. "With so many data sources, distribution channels, demands for aggregation and analytics, surges in data volumes and complex dynamics between the flows, we need to manage 'data in motion' and give up the notion that data is somehow stored. It's dynamic, not static," says Michael Di Stefano, vice president and architect for financial services at GemStone Systems
...
There is even some debate over how small a unit of work can be put on today's grids. Di Stefano at GemStone, for example, says, "One client has gone from 200 trades per second in a program trading application to more than 6,000 trades per second. This shows what the technology can do."

Yep, the writing is on the wall. Oracle knows it too.

http://www.google.com/search?hl=en&q=oracle+buys+tangosol&btnG=Google+Search"

Me:
"Good points. If it is in-memory it would be faster. I have not had the pleasure to work on such a system.

I do disagree with the database concerns being a subset of application concerns. The data drives the app. We’re probably getting religious at this point (or am I)."

Mr. M:
"‘The data drives the app.”

Exactly, but who’s to say where the data comes from or in what format? My application data may reside completely in xml files, or maybe I get it from some third party web services a la the en vogue “mashup.” Heck, I may not even need to worry about a database anymore…. http://www.amazon.com/gp/browse.html?node=16427261 The database is only one particular concern of the overall application. And it’s the application that matters. Data is useless if it just sits on a disk somewhere. It’s the ways in which the application lets the users view and manipulate the data that adds value to the business.

Yep, definitely a different type of thinking between application developers and database developers."

Me:
"Definitely religious now.

Applications come and go, data stays the same. Think Green Screens, EJBs, Ruby…what’s next?"

Mr. M:
"'Applications come and go'

Exactly. Businesses are not static, nor are the markets they compete in. Changing applications are a function of changing business processes and changing markets.

'data stays the same.'

Nonsense. Otherwise UPDATE would not be an SQL reserved word. If you mean database technology stays the same, well, I’m more inclined to agree with that.

'Think Green Screens, EJBs, Ruby...what’s next?'

Whatever comes along to let the business more effectively respond to current market realities. Application platforms have evolved much faster than database platforms have. They’ve had to, their sphere of operation is much broader than that of databases, this is only natural, they deal with much broader concerns than do databases. Databases in the internet era function in essentially the same role they did in the era of dumb terminals. Clearly application platforms have evolved orders of magnitude more. Hence the statement, database concerns are a subset of application concerns.

Here’s a simple test….if I take some business application and I’m forced to throw away one or the other, either the database or the appl- wait a second, it doesn’t even make sense to finish it, does it? The business can live without the database. I could do all kinds of things with the data, I could stick it anywhere. The business can’t live without the application though. Another way to look at is, what do the business users look at, test, approve, and use? The database? Of course not, they look at the application. They could care less whether the data sits on disk in an RDBMS, xml, or flat files."

Me:
"We obviously violently disagree.

Without the database (and I use database and data interchangebly), the business could no longer function. The app is meaningless. How would you contact your customer? You couldn’t find it.

'Exactly. Businesses are not static, nor are the markets they compete in. Changing applications are a function of changing business processes and changing markets.'

Poorly designed applications…that is all."

A Feisty Colleague:
"Using data and database interchangeably is incorrect. A database is a mechanism for data storage. XML data sets and flat files are mechanisms for data storage, too. So is a file cabinet, because, the data doesn’t have to be electronic, it could be … gasp! … on paper, and the application to use that data would be hands for holding the paper and a pencil to update and add data to the page."

Me:
"No it isn’t. I take into account xml files, flat files, web services (but not paper, unless it’s scanned) and all that. It would be consumed by the database and then accessed by the application via SQL.

(that’s for Mr. M and the feisty one)"

At which point someone forwarded the home page for Oracle's TimesTen In-Memory Database.

Me:
"A database on/in the mid-tier...Perfect!"

Mr. M:
"Implicit acknowledgment that disk IO operations that come with traditional database access simply can’t match the performance of in-memory data access (a point which you previously were unconvinced of but now seem perfectly accepting of the idea once you see it’s got Oracle’s imprimatur on it).

Of course, why any application developer would want to program against an SQL interface if they weren’t forced to is beyond me. It is orthogonal to the programming model of most application platform languages.

Surely Oracle recognize this fact too or they wouldn’t be buying Tangosol and other data grid technologies. Of course, most of those products are far more technically advanced than TimesTen or anything Oracle has in that space.

Incidentally, it’s illustrative to note that Coherence and other products like it were for the most part designed and built by application programmers. The development of all these products is pretty much driven by the needs of the large investment banks on Wall Street. These trading applications simply had too many concurrent transactions to use an RDBMS (a problem quite a number of public domains now share, most famously google.com, nope, no RDBMS there, yet miraculously there is still data). The database just simply would not scale to such a degree. So the application developers, by necessity, came up with an alternate solution that did work, a fully transactional cache of data replicated across a cluster with node numbers in the thousands, and no relational model whatsoever to speak of. A perfect example of how database concerns are only one, sometimes small, concern amongst many that application developers must be aware of and ready to solve."

Me:
"Like you said initially, the top 1%.

Many of us will never touch a system like this.

I will certainly concede that it is faster (still would love to see benchmarking though), but that still leaves 99% of the applications out there that do not require that kind of performance."

Me (again):
"And don’t forget, I use data and database interchangeably. Applications are nothing without the data right?

As to the object/relational impedance mismatch...well, more people that don’t know how to work in sets. Looping is what they understand. I understand the application side more than you seem to give me credit for.

I’m not saying applications aren’t important, they are. Data (databases) and applications go hand in hand. If the application went away though, they could still access their data via SELECT statements (yes, via an application client tool), however painful that may be. Applications make retrieving data that much easier for our users.

If anyone wants to unsubscribe from this mailing list, just let us know. This is fun for me (I’m guessing Mr. M too)."

Needless to say it was a fun day. It didn't get [too] personal. More than anything I'm happy to have an equally passionate colleague.

Besides, he claims he was just fracking around with me. ;)

Labels: database, development, funny, partition, work

  ¶ 2/20/2008 10:29:00 PM 5 Comments Links to this post

  MySQL Friday Each month we have an IT All-Hands meeting.

Last month I was promoted to Senior Vice President (SVP), because of my superior management techniques.

Today I was promoted to CEO! Unfortunately it only lasted for a few minutes. I happen to resemble our new CEO (and I'm always pining for a promotion) and they thought it would be funny (again) to bring me up.

I hugged the guy behind me, shook hands with people next to me and ran up to the front. I wanted to shriek, like the people do on The Price is Right, but I didn't have it in me. You gotta have fun at work right?

Well, after that it got serious. Our new Director (at WellCare, Directors are executives, one step up from managers and one below VPs) who heads our architecture team (and release management) got up to discuss where he would be taking us.

Slide one:
From 3 database engines to 1.
From 4 programming languages to 2.
From 3 OSs to 1.

Wanna guess what question I had?

"So, what database engine are we going to use?"

I knew the answer, but I take every single opportunity I get to make my point.

"MySQL."

Being on the datawarehouse team, I was confident that Oracle was not going away.

He went on to explain:

"Legacy applications would be maintained but everything going forward would be done in MySQL."

A flurry of questions came from the crowd so I was unable to followup immediately. I could feel the room come alive...it was weird (I think I'm still hopped up from the events that took place today).

Our CIO asked if there were any more questions or comments.

I spoke up.

I have two points.
1. If it's about cost, move all of the one-off applications into just a few Oracle instances. From what I can tell, we have somewhere in the neighborhood of 100. Let's say 5 databases, datawarehouse, our production OLTP and one for others. All you need to do is assign them different schemas, voila! Cost is much lower and there is a very big chance to reuse code.
2. Actually, I can't remember what my other point was. I think it had something to do with putting the logic in the database, that Java was the fad a few years ago, Ruby was the big thing now, what would it be in 5 years? Will we have to rewrite all of the logic then? (I guess I do sorta remember).

After that, someone asked about the two programming languages. Not a great answer from the crowd's reaction. Then someone asked about the OS.

The crowd was riotous (if that's a word). The CIO had to calm us all down.

I made a remark that he hadn't danced yet (one of our former hazing techniques for new employees) because I didn't want it to be completely personal, or just to ease something that I started.

After the meeting, I spoke with the Director. Oracle will be gone in 20 years because of the open source databases, it's being commoditized (not sure what that means). SOA is the wave of the future.

It was a polite conversation. I told him I look forward to learning from him but that I will probably never be sold on that idea. Fewer moving parts, simplicity, that's what I want.

I then spoke with the CIO, told him that once the decision was made, I would support it and keep my mouth shut (or find a new job).

I sent an email to the VP of the Director's group (after a couple of beers...idiot!) explaining my rationale.

One of the biggest reasons we chose to come to Tampa, to WellCare specifically, was because it was so young and immature. I would have the opportunity, if I could prove myself, to shape the future of IT here.

It's nice to have a voice.

Anyway, it's Friday, I'm prepped to spend all weekend at work to get this project delivered that was due in November. Have a good weekend!

Labels: cio, database, funny, mysql, oracle, sql, work

  ¶ 2/01/2008 10:52:00 PM 2 Comments Links to this post

  Corporate Life One of the biggest reasons we decided to move down to Tampa (November 2006) was so that I can work in a "real" corporate environment (i.e. more than a few hundred people). WellCare has about 3,000 employees, IT makes up about 250.

It feels like I've experienced about every event I could have imagined:
1. In January of 2007, a new CIO/SVP was hired and promptly restructured (replaced the VPs) the IT department.
2. In October of 2007, we had a nifty FBI raid.
3. January of 2008, we appear to be losing our CEO, CFO and General Counsel.

Now I just need to wait for the merger/acquisition and I will have experienced just about everything!

Labels: funny, work

  ¶ 1/23/2008 09:24:00 PM 1 Comments Links to this post

  The Countdown Timer So I found a handy little countdown timer from this site so that I can replicate my hand made sign in my cube. This all stems from a previous incident.

Now I'll have a reminder at work and on the blog not to touch that which is out of scope.

Labels: discipline, funny, humility

  ¶ 9/11/2007 12:36:00 AM 0 Comments Links to this post