SQL Developer: Turn Off "Autogenerate GROUP BY"

This is more for me since I seem to install it quite regularly.

I've been snagging the SQL from OBIEE query logs (nqquery.log), which doesn't come out too pretty.

So that I don't have to manually format 400 lines of SQL, I created a formatting template. Ctl - F7 and voila!

I do go back in and make small changes, which is why I am writing this. As I scroll down through the file and indent or change code, the GROUP BY clause is auto-generated, which is annoying to me.

To turn it off is easy (if you can remember, which I can't, which is why I write this).

Go to Tools - Preferences then look for Code Editor:



Expand that group and then go to Completion Insight:



Uncheck the box next to the arrow and you're done.

My hope is that in the next version of SQL Developer, we'll be able to call the SQL Beautifuler from the command line. That would be pure awesome.

OBIEE 11g Install - Part I

As you may know, OBIEE 11g was released not too long ago.

I downloaded it the day it was available, and have been trying every since to 1, find the time to install it and 2, not break the install process.

Environment
Guest: Windows XP Professional (an old image I had lying around)
VirtualBox 3.2.8
Host: Ubuntu Karmic Koala
Memory: 1.5 GB

In short, my laptop.

I started with the database, 11gR2 32 bit for Windows. No problems there.

As is my MO, I didn't read the installation instructions the first time I fired up the OBIEE installation. I was quickly met with the database connect string. Easy enough, machine:port:service_name.

What's this? You want a specific user? Bollocks.

I let it rest for a couple of days.

Meanwhile, a colleague asked a question about which database he could use. I asked if a version was specified and then quickly scanned the docs. No specific mention of a version...so I said Oracle XE, no 11gR2, no, XE, no, 11gR2. 11gR2, yeah, that's it.

That's when I finally decided to read the installation instructions...and found out I had missed one of the downloads.



Oh, you mean that one?

The Repository Creation Utility. Interesting.

I had heard that some of the new version would be database driven, I had just assumed (yes, I know about assuming) it was more RPD related (and it still may be).

Anyway, that took all of 15 seconds to install.

Back to the installer...and I don't learn any lessons, I tried to install it from my virtual folder, instead of from the "actual" disk. So, it took 4 times before I finally figured that out.

I copied the zip files to the disk, unzipped them and installed the sucker. By install I mean I am in step 12 of 13, Configuring BI Domain. That's the furthest I have gotten so I'm calling it a success.

Now to wipe the disk and start from scratch.

Fun with SQL - Part 59

I got an email today titled, "Your Favorite Movie."

It claims to successfully calculate your favorite movie by picking a number, from 1 to 9, then doing some calculations and applying the result to a list of movies.

Years ago...my first boss, got a similar email and began working through it via SQL. I was very impressed, both by the idea of using SQL and that he could do it in SQL. He was (is) a smart, creative guy.

I'm sure you've seen this one before:

Your Favorite Movie

Try this test. Scroll down and do the quiz as it instructs and find out what movie is your favorite. This amazing math quiz can likely predict which of 18 films you would enjoy the most. Don't ask me how but it really works!

Pick a number from 1-9.

Multiply by 3.

Add 3.

Multiply by 3 again.

Now add the two digits together to find your predicted favorite movie in the list of 18 movies below.

Mine was "Gone with the Wind" - exactly right! So be honest, and do it before you scroll down to see the list below. It's easy and it works.

How do you do this in SQL? It's not that hard really. First, get 1-9. SELECT from DUAL will accomplish that.

SELECT rownum
FROM dual
  CONNECT BY LEVEL <= 9

Then you just need to apply the calculations. First go, I put the calcs in the outer query:

SELECT rn, ( ( ( rn * 3 ) + 3 ) * 3 ) calc_#
FROM
  (
    SELECT rownum rn
    FROM dual
      CONNECT BY LEVEL <= 9
  )
/

    RN     CALC_#
------ ----------
     1         18
     2         27
     3         36
     4         45
     5         54
     6         63
     7         72
     8         81
     9         90

A pattern jumps out at me immediately...

So I move it to the inner query and use SUBSTR to get each number and then add them together. I am sure there are better ways...this was quick and dirty.

SELECT
  rn,
  SUBSTR( t, 1, 1 ) + SUBSTR( t, 2, 1 ) total
FROM
  (
    SELECT
      rownum rn,
      ( ( ( rownum * 3 ) + 3 ) * 3 ) t
    FROM dual
      CONNECT BY LEVEL <= 9
  )
/

The results reveal the pattern rather quickly.

    RN      TOTAL
------ ----------
     1          9
     2          9
     3          9
     4          9
     5          9
     6          9
     7          9
     8          9
     9          9

Of course #9 is the movie that you would never pick. It's funny though:

1. Gone With The Wind
2. E.T.
3. Beverly Hills Cop
4. Star Wars
5. Forrest Gump
6. The Good, The Bad, and the Ugly
7. Jaws
8. Grease
9. The Joy of Anal Sex With A Goat
10. Casablanca
11. Jurassic Park
12. Shrek
13. Pirates of the Caribbean
14. Titanic
15. Raiders Of The Lost Ark
16. Home Alone
17. Mrs. Doubtfire
18. Toy Story

OBIEE 11g Released

I'm not really out in front of this one, but I'll do the obligatory post.

11g was announced back in early July in the UK, followed shortly by an announcement here in the States. Every since then, I'll go to OTN, downloads, OBIEE to see if was generally available. I've done this every day for the past month. On Friday, I did the same thing and 10.1.3.4 was still there. I then got an email from my boss that evening saying it had been released. I guess I can't first for everything.

To download the latest and greatest, go here. The Overview is here. And finally the documentation.

I downloaded it on Friday evening (nerd) but haven't installed it just yet. I'm hoping to get to it this week. I wouldn't say I'm excited...I'm still in my learning phase...so it just means a bit more work. I'm sure it will be fun though.

Mr. Rittman, I believe, has written a book on 11g, which means he's had it for quite some time. Get on over there to see a host of articles pertaining to the new release. I'll start with the New Features section, the install, and probably head on over there.

Twitter as Social Support

Many of you know I went through a bit of a family crisis recently. My 5 1/2 year daughter Kate was hospitalized, then put in the pediatric intensive care unit (PICU) and soon after that, put on a ventilator. Not only is it remarkable how fast it all went down but also how fast she recovered. Within a little over 24 hours after being removed from the ventilator, she was sent home.

Over the years, people have asked how I can air my grievances/likes/dislikes/personal stuff/etc openly via this blog. I have never really had a good answer...usually something about taking control of my online persona comes out...

Over these same few years, I've been privately contacted by many people. Some completely random; like recently, a mother who's 1 year old daughter was going in for the first of a few syndactyly surgeries and then others, who read the blog, who have similar family situations (a sick child) or know someone close to them that does.

I've gotten quite a lot out of those encounters...something I would never have been able to get had I just kept to myself.

So here's one argument for putting personal stuff "out there." Support.






















































































In case you weren't counting, there are 83 replies there. A couple of duplicates. Most of you, I have never met in real life.

Pardon my language, but how fucking cool is that? You all rock!