Oracle + Open Source

two worlds inexorably colliding

Archive for November 8th, 2007

MySQL AB At Oracle Open World?

Nov 8

Posted by Sean Hull in mysql, open-source, oracle | 1 Comment

Incredible, but true.  It seems that MySQL AB will be exhibiting next week at Oracle Open World.  This of course isn’t the first time a competitor would advertise or exhibit on it’s rival’s home turf.  Still it certainly signals a changing landscape, and heats up the battle for market share.

Here’s a longer list of exhibitors at the conference.  I don’t see Enterprise DB there, but anything’s possible.  You will see RedHat as well as Suse, now owned by Novell, represented there as well.  Also if you make it to the conference, be sure to visit the Oracle pavilion section, where there are sure to be smaller booths for the Open Source Group, as well as Oracle Unbreakable Linux Support program.

Sum, Decode + Reworking a Group By

Nov 8

Posted by Sean Hull in oracle | No Comments

SQL can be tricky. Here’s a way to display date based summations across one row of output…

Suppose you have quarterly totals for 2006 like this:

SELECT TO_CHAR(orderdt, 'Q') Q, sum (price) the_total
FROM my_orders
WHERE orderdt >= '01-JAN-2006'
AND orderdt < '01-JAN-2007'
GROUP BY TO_CHAR(orderdt, 'Q')
ORDER BY 1;

The results would be like this:


Q THE_TOTAL
- -----------
1 1000
2 1500
3 1300
4 2000

Now let’s throw some SUM and DECODE functions into the mix.


SELECT
SUM (DECODE(TO_DATE(orderdt, 'Q'), '1', sale_price, 0)) Q1,
SUM (DECODE(TO_DATE(orderdt, 'Q'), '2', sale_price, 0)) Q2,
SUM (DECODE(TO_DATE(orderdt, 'Q'), '3', sale_price, 0)) Q3,
SUM (DECODE(TO_DATE(orderdt, 'Q'), '4', sale_price, 0)) Q4
FROM my_orders
WHERE orderdt > '01-JAN-2006'
AND orderdt < '01-JAN-2007'
GROUP BY TO_CHAR(orderdt, 'Q');

Results would then look formated I think how you would like:


Q1         Q2         Q3         Q4
---------- ---------- ---------- ----------
1000       1500       1300       2000