Tuesday, October 04, 2005

You can never have too many tables

There is no problem that cannot be solved with a loop and some dynamic SQL.

Problem: Your report can return rows for more than one feature code. How on Earth are you going to store the results?

Solution: Loop through the FEATURES table, creating three dynamically named tables for each feature code. Problem solved.

BEGIN
FOR rec IN
    ( SELECT f.short_code ft FROM features f
      WHERE  f.chargeable = 'Y' )
LOOP
    BEGIN
        EXECUTE IMMEDIATE 'drop table r_t8_' || rec.ft ;
    EXCEPTION
        WHEN OTHERS THEN NULL;
    END;

    EXECUTE IMMEDIATE 'create table r_t8_' || rec.ft || '(' ||
    'report_type varchar2(50),' ||
    'report_date date,' ||
    'switch_id number,' ||
    'feat_count number,' ||
    'rental_rate number,' ||
    'discount number,' ||
    'rental_rating number)' ;

    BEGIN
        EXECUTE IMMEDIATE 'drop table r_t7_' || rec.ft ;
    EXCEPTION
        WHEN OTHERS THEN NULL;
    END;

    EXECUTE IMMEDIATE 'create table r_t7_' || rec.ft || '(' ||
    'report_type varchar2(50),' ||
    'report_date date,' ||
    'switch_id number,' ||
    'feat_count number,' ||
    'rental_rate number,' ||
    'discount number,' ||
    'rental_rating number)' ;

    BEGIN
        EXECUTE IMMEDIATE 'drop table r_t9_' || rec.ft ;
    EXCEPTION WHEN OTHERS THEN
        NULL;
    END;

    IF rec.ft <> 'ZZ' THEN
        EXECUTE IMMEDIATE 'create table r_t9_' || rec.ft || '(' ||
        'report_type varchar2(50),' ||
        'report_date date,' ||
        'switch_id number,' ||
        'feat_count number,' ||
        'rental_rate number,' ||
        'discount number,' ||
        'rental_rating number)' ;
    END IF;
END LOOP;
END;
/

Now, how to write those reports so they populate the right tables? I know...

4 comments:

Noons said...

I'm having trouble closing my mouth: the jaw keeps dropping...

Adrian said...

I could tell you about a particularly large merchant banking organisation that struck on the same idea.

Rather than including an internal business unit in the key of their tables they created a complete copy of every single table in a certain anonymous enterprise back end system.

BU_01_Trades
BU_02_Trades
BU_0x_Trades

etc. etc. etc.

Clearly there is some designer "out there" with years of experience peddling this idea.

William Robertson said...

> You should seriously rethink this

LOL! You think we make this stuff up?

This is some code I found at my current site while having a clearout. It was already superceded by some marginally less crappy code before I joined, although it was still present in the live install scripts, which is how I came to notice it. It has now been deleted, trust me.

Adrian said...

You don't need a distributed database for that to be a bad idea. A random number is not going to work for a key on a single node either.