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.
