A gripe about SQL

As a SQL programmer, sometimes I feel invincible that there’s nothing I can’t do with Oracle.
But then comes the pivoting query, SQL sucks. It’s not just Oracle, but SQL language altogether.
Enough workaround, solutions have been suggested, that I don’t want to repeat here. Just search “Pivot” at AskTom. Mind you, each of the solutions has major drawbacks or “Gotchas” that you want to think through before deploying such a solution on a production system. One limitation is you have to know how many columns you will have and another is dynamically generated SQL could exceed limit.
That’s why there are so many Excel users out there swear that Excel is the best thing since sliced bread, oh, except for the 256 column limit.
The BI tools are not farring much better. Cognos sucks at a “wide” report, having too many columns, will take much longer to run especially when you throw in more measures. Interestingly, a “long” report would do ok. So if you have more columns than rows in a Cognos Pivot table, you better swap the columns and rows for performance sake.
I don’t know, maybe pivoting is inherently a hard problem?

Advertisements

4 Responses to “A gripe about SQL”

  1. Mike Lee Says:

    Well DJ, you are right about one thing and wrong about another. First, you are right, Cognos Sucks. It’s like going to the fair standing in front of two coca-cola stands, where the one on the left (Cognos) is selling cokes for $20 a can. The one on the right is selling the same thing for $1. There are a bunch of executives standing in line at the $20 counter because they “think it’s better”, but they have no fricking clue why. Anyway, you are wrong about pivot tables. You have to go and check out Microsoft’s new SQL Server 2005 built in Pivot table functionality. It’s a no brainer you turd!

  2. DJ Says:

    Mike,

    I know Cognos sucks for Oracle, big time. Does it suck for SQL Server as well? I always assumed Cognos is more geared towards SQL Server.
    Anyway, I will check out the pivot table functionality you mentioned in SQL Server. MS did invent Excel, best thing since slice bread…

  3. Mike Lee Says:

    Hey your a cool guy DJ…
    By the way, since you like Excel so much, there are some integrations with Excel in the pivot table features. Check it out man!

  4. SQL Server pivot query « DJ’s blog Says:

    […] I blogged about Pivot query, sometimes referred to as a cross-tab query, a while ago.Mike has mentioned pivot syntax in SQL Server here. I checked it out. I wouldn’t describe it as cool or glamorous, but it is a neat feature that I hope Oracle will adopt. I know, not easy, for some pride issues. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: