Virtual Columns in Oracle 11g – Ashes to Ashes

The test series between the two pre-eminent nations in world cricket has finished. But never mind India and South Africa, England have just won the Ashes. In Australia. 3–1. Look, it’s not gloating. The last time England won in Australia was almost a quarter of a century ago. As a nation, the English have learned to savour sporting success as we never know when (if) it will come around again.
By happy chance, Cricket batting and bowling stats lend themselves quite neatly to being used as an example of Virtual Columns in Oracle 11.
So, at the risk of alienating any Australians reading ( and reducing my chances of getting served in a bar anywhere in the Greater London Area)…

For readers not au fait with the game of cricket, let’s start with the calculations themselves.
A batting average is the number of runs scored divided by the number of innings batted ( less the number of innings where the batsmen was not dismissed – i.e. “not outs”).
A bowling average is simply the number of runs conceded by the bowler divided by the number of wickets taken.
Let’s have a go with the batting first :

CREATE TABLE ashes_batting(
    player VARCHAR2(50),
    matches NUMBER(1),
    inns NUMBER(2),
    no NUMBER(2),
    runs NUMBER(3),
    ave AS (ROUND( runs / ( inns - no),2))
     )
/

Now to populate the table :

DECLARE
    PROCEDURE ins( plr IN VARCHAR2, mat IN NUMBER, inn IN NUMBER,
        no IN NUMBER, runs IN NUMBER) IS
    BEGIN
        INSERT INTO ashes_batting( player, matches, inns, no, runs)
        VALUES( plr, mat, inn, no, runs);
    END ins;
BEGIN
    ins( 'STRAUSS', 5, 7, 0, 307);
    ins( 'COOK', 5, 7, 1, 766);
    ins( 'TROTT', 5, 7, 2, 445);
    ins( 'PIETERSON', 5, 6, 0, 360);
    ins( 'COLLINGWOOD', 5, 6, 0, 83);
    ins( 'BELL', 5, 6, 1, 329);
    ins( 'PRIOR', 5, 6, 1, 252);
    ins( 'HUSSEY', 5, 9, 0, 570);
    ins( 'WATSON', 5, 10, 1, 435);
    ins( 'HADDIN', 5, 9, 1, 360);
END;
/

The ave column is our Virtual Column. The values in this column are not held in the table but are calculated when queried. The great thing is that you can use them just like any other column :

column player format a30
column inns format 9999
column no format 99
column runs format 9999
column ave format 999.99
SELECT player, inns, no, runs, ave
FROM ashes_batting
WHERE ave > 50
ORDER BY ave DESC
/
PLAYER				INNS  NO  RUNS	   AVE
------------------------------ ----- --- ----- -------
COOK				   7   1   766	127.67
TROTT				   7   2   445	 89.00
BELL				   6   1   329	 65.80
HUSSEY				   9   0   570	 63.33
PIETERSON			   6   0   360	 60.00
PRIOR				   6   1   252	 50.40

6 rows selected.

SQL> 

You don’t have to define the calculation for a virtual column in-line in the table creation statement. You could just call a database function as long as it is defined as defined as deterministic i.e. the function always returns the same value for the same input parameters ( and you use the DETERMINISTIC keyword).
That’s pretty good for us here as we’re just doing some simple maths, we’re not relying on data which may cause our return value to change ( such as a value in a table). On to the bowling. Obviously we need to create a function before we reference it in a table creation statement :

CREATE OR REPLACE FUNCTION get_bowl_ave_fn( p_runs IN NUMBER, p_wkts IN NUMBER)
    RETURN NUMBER DETERMINISTIC AS
BEGIN
    RETURN ROUND(p_runs / p_wkts, 2);
END;
/

Now for the table itself …

CREATE TABLE ashes_bowling (
    player VARCHAR2(50),
    runs NUMBER(4),
    wkts NUMBER(4),
    ave AS ( get_bowl_ave_fn( p_runs => runs, p_wkts => wkts))
    )
/

… and now insert some records …

DECLARE
    PROCEDURE ins( plr IN VARCHAR2, runs IN NUMBER, wkts IN NUMBER) AS
        BEGIN
            INSERT INTO ashes_bowling( player, runs, wkts)
            VALUES( plr, runs, wkts);
        END ins;
BEGIN
    ins( 'BRESNAN', 215, 11);
    ins( 'TREMLETT', 397, 17);
    ins( 'ANDERSON', 625, 24);
    ins( 'FINN', 464, 14);
    ins( 'SWANN', 597, 15);
    ins( 'HARRIS', 281, 11);
    ins( 'SIDDLE', 484, 14);
    ins( 'JOHNSON', 554, 15);
END;
/

As we can see from the query below, the ave column works in exactly the same way :

column player format a30
column runs format 9999
column wkts format 99
column ave format 999.99
SELECT player, runs, wkts, ave
FROM ashes_bowling
ORDER BY ave
/
PLAYER				RUNS WKTS     AVE
------------------------------ ----- ---- -------
BRESNAN 			 215   11   19.55
TREMLETT			 397   17   23.35
HARRIS				 281   11   25.55
ANDERSON			 625   24   26.04
FINN				 464   14   33.14
SIDDLE				 484   14   34.57
JOHNSON 			 554   15   36.93
SWANN				 597   15   39.80

8 rows selected.

SQL> 

Let’s extend this a bit. Say you wanted to know the strike rate for each bowler – i.e. the number of balls bowled per wicket taken.
We’d need another virtual column to work that out. In fact, due to the fact that we’ve only got the number of overs bowled, we’ll need a virtual column to work out the number of balls as well. Function first …

CREATE OR REPLACE FUNCTION overs_to_balls_fn ( p_overs IN VARCHAR2)
    RETURN NUMBER DETERMINISTIC AS
    l_ov_balls SIMPLE_INTEGER := 0;
    l_part_ovs SIMPLE_INTEGER := 0;
BEGIN
    --
    -- There are 6 balls in an over.
    -- Overs are recorded in the format over.odd_balls
    -- e.g. 5 overs and 2 balls is recorded as 5.2. So, we need
    -- to split the input string into the component parts before and after
    -- the decimal point
    --
    l_ov_balls := SUBSTR( p_overs, 1, INSTR( p_overs, '.',1,1) -1 ) * 6;
    l_part_ovs := SUBSTR( p_overs, INSTR( p_overs, '.',-1) +1 );
    RETURN l_ov_balls + l_part_ovs;
END;
/

Now we should be able to add the strike rate column. What could possibly go wrong ?

CREATE TABLE ashes_bowling2 (
    player VARCHAR2(50),
    overs VARCHAR2(5),
    runs NUMBER(4),
    wkts NUMBER(2),
    ave AS ( runs / wkts),
    balls AS (overs_to_balls_fn( overs)),
    srate AS (balls / wkts)
    )
/
    balls AS (overs_to_balls_fn( overs)),
    *
ERROR at line 6:
ORA-54012: virtual column is referenced in a column expression

Hmmm, so you can’t reference a Virtual Column when defining another Virtual Column. Interestingly, the line it objects to in the error message isn’t the line that’s causing the problem.
The real issue is :

srate AS ( balls / wkts)

I know this because the table creation script works without it.
Anyway, how can we use a Virtual Column to get that all important Strike Rate ? Well, we need to do something like this :

CREATE TABLE ashes_bowling2 (
    player VARCHAR2(50),
    overs VARCHAR2(5),
    runs NUMBER(4),
    wkts NUMBER(2),
    ave AS ( runs / wkts),
    srate AS (overs_to_balls_fn( overs) / wkts)
    )
/

That’s better. Now to populate the table :

DECLARE
    PROCEDURE ins( plr IN VARCHAR2, overs IN VARCHAR2, runs IN NUMBER, wkts IN NUMBER) AS
        BEGIN
            INSERT INTO ashes_bowling2( player, overs, runs, wkts)
            VALUES( plr, overs, runs, wkts);
        END ins;
BEGIN
    ins( 'BRESNAN', '82.4', 215, 11);
    ins( 'TREMLETT', '122.3', 397, 17);
    ins( 'ANDERSON', '213.1', 625, 24);
    ins( 'FINN', '107.4', 464, 14);
    ins( 'SWANN', '219.1', 597, 15);
    ins( 'HARRIS', '83.4', 281, 11);
    ins( 'SIDDLE', '147.1', 484, 14);
    ins( 'JOHNSON', '136.3', 554, 15);
END;
/

And finally, to query it :

column player format a30
column runs format 9999
column wkts format 99
column ave format 999.99
column srate format 999.99
SELECT player, runs, wkts, ave, srate
FROM ashes_bowling2
ORDER BY ave
/

PLAYER				RUNS WKTS     AVE   SRATE
------------------------------ ----- ---- ------- -------
BRESNAN 			 215   11   19.55   45.09
TREMLETT			 397   17   23.35   43.24
HARRIS				 281   11   25.55   45.64
ANDERSON			 625   24   26.04   53.29
FINN				 464   14   33.14   46.14
SIDDLE				 484   14   34.57   63.07
JOHNSON 			 554   15   36.93   54.60
SWANN				 597   15   39.80   87.67

8 rows selected.

SQL> 

As my lovely ( Welsh) girlfriend is beginning to roll her eyes at this display English patriotism, it’s time to sign off in the interests of domestic harmony. For some, reason I’m really not looking forward to the Six Nations.

About these ads

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