rob@XE:11g2> CREATE TABLE VC_nfl2012_scores AS

  2  SELECT

  3    team,

  4    SUBSTR(division,1,4) AS div,

  5    game,

  6    opponent AS opp,

  7    venue VARCHAR2(4) GENERATED ALWAYS AS

  8      DECODE(SUBSTR(opponent,1,1), '@','AWAY', 'HOME') VIRTUAL,

  9    ptsf,

 10    ptsa,

 11    (CASE

 12       WHEN ptsf > ptsa THEN 'WIN'

 13       WHEN ptsf < ptsa THEN 'LOSS'

 14       WHEN ptsf = ptsa THEN 'TIE'

 15     END) wlt

 16  FROM

 17  (

 18    SELECT *

 19    FROM ext_nfl2012_scores

 20    UNPIVOT

 21    (

 22      (opponent, ptsf, ptsa) FOR game IN

 23      (

 24        (opponent1, points_for1, points_against1) AS 'wk1',

 25        (opponent2, points_for2, points_against2) AS 'wk2',

 26        (opponent3, points_for3, points_against3) AS 'wk3',

 27        (opponent4, points_for4, points_against4) AS 'wk4',

 28        (opponent5, points_for5, points_against5) AS 'wk5',

 29        (opponent6, points_for6, points_against6) AS 'wk6',

 30        (opponent7, points_for7, points_against7) AS 'wk7',

 31        (opponent8, points_for8, points_against8) AS 'wk8',

 32        (opponent9, points_for9, points_against9) AS 'wk9',

 33        (opponent10, points_for10, points_against10) AS 'wk10',

 34        (opponent11, points_for11, points_against11) AS 'wk11',

 35        (opponent12, points_for12, points_against12) AS 'wk12',

 36        (opponent13, points_for13, points_against13) AS 'wk13',

 37        (opponent14, points_for14, points_against14) AS 'wk14',

 38        (opponent15, points_for15, points_against15) AS 'wk15',

 39        (opponent16, points_for16, points_against16) AS 'wk16'

 40      )

 41    )

 42  );

  venue VARCHAR2(4) GENERATED ALWAYS AS

        *

ERROR at line 7:

ORA-00923: FROM keyword not found where expected

-- Remake the table without the two computed columns... --

rob@XE:11g2> CREATE TABLE VC_nfl2012_scores AS

  2  SELECT

  3    team,

  4    SUBSTR(division,1,4) AS div,

  5    game,

  6    opponent AS opp,

  7    ptsf,

  8    ptsa

  9  FROM

 10  (

 11    SELECT *

 12    FROM ext_nfl2012_scores

 13    UNPIVOT

 14    (

 15      (opponent, ptsf, ptsa) FOR game IN

 16      (

 17        (opponent1, points_for1, points_against1) AS 'wk1',

 18        (opponent2, points_for2, points_against2) AS 'wk2',

 19        (opponent3, points_for3, points_against3) AS 'wk3',

 20        (opponent4, points_for4, points_against4) AS 'wk4',

 21        (opponent5, points_for5, points_against5) AS 'wk5',

 22        (opponent6, points_for6, points_against6) AS 'wk6',

 23        (opponent7, points_for7, points_against7) AS 'wk7',

 24        (opponent8, points_for8, points_against8) AS 'wk8',

 25        (opponent9, points_for9, points_against9) AS 'wk9',

 26        (opponent10, points_for10, points_against10) AS 'wk10',

 27        (opponent11, points_for11, points_against11) AS 'wk11',

 28        (opponent12, points_for12, points_against12) AS 'wk12',

 29        (opponent13, points_for13, points_against13) AS 'wk13',

 30        (opponent14, points_for14, points_against14) AS 'wk14',

 31        (opponent15, points_for15, points_against15) AS 'wk15',

 32        (opponent16, points_for16, points_against16) AS 'wk16'

 33      )

 34    )

 35  );

Table created.

-- Add the virtual columns... --

rob@XE:11g2> ALTER TABLE VC_nfl2012_scores

  2  ADD venue VARCHAR2(4) GENERATED ALWAYS AS

  3      (DECODE(SUBSTR(opp,1,1), '@','AWAY', 'HOME')) VIRTUAL;

Table altered.

ROB@XE:11g2> ALTER TABLE VC_nfl2012_scores

  2  ADD wlt VARCHAR2(4) GENERATED ALWAYS AS

  3      (CASE

  4        WHEN ptsf > ptsa THEN 'WIN'

  5        WHEN ptsf < ptsa THEN 'LOSS'

  6        WHEN ptsf = ptsa THEN 'TIE'

  7       END) VIRTUAL;

Table altered.

rob@XE:11g2> select * from VC_nfl2012_scores

  2  where rownum < 35;

TEAM DIV              GAME OPP        PTSF       PTSA VENU WLT

~~~~ ~~~~~~~~~~~~~~~~ ~~~~ ~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~ ~~~~

NYG  NFCE             wk1  DAL          17         24 HOME LOSS

NYG  NFCE             wk2  TB           41         34 HOME WIN

NYG  NFCE             wk3  @CAR         36          7 AWAY WIN

NYG  NFCE             wk4  @PHI         17         19 AWAY LOSS

NYG  NFCE             wk5  CLE          41         27 HOME WIN

NYG  NFCE             wk6  @SF          26          3 AWAY WIN

NYG  NFCE             wk7  WAS          27         23 HOME WIN

NYG  NFCE             wk8  @DAL         29         24 AWAY WIN

NYG  NFCE             wk9  PIT          20         24 HOME LOSS

NYG  NFCE             wk10 @CIN         13         31 AWAY LOSS

NYG  NFCE             wk11 GB           38         10 HOME WIN

NYG  NFCE             wk12 @WAS         16         17 AWAY LOSS

NYG  NFCE             wk13 NO           52         27 HOME WIN

NYG  NFCE             wk14 @ATL          0         34 AWAY LOSS

NYG  NFCE             wk15 @BAL         14         33 AWAY LOSS

NYG  NFCE             wk16 PHI          42          7 HOME WIN

SF   NFCW             wk1  @GB          30         22 AWAY WIN

SF   NFCW             wk2  DET          27         19 HOME WIN

SF   NFCW             wk3  @MIN         13         24 AWAY LOSS

SF   NFCW             wk4  @NYJ         34          0 AWAY WIN

SF   NFCW             wk5  BUF          45          3 HOME WIN

SF   NFCW             wk6  NYG           3         26 HOME LOSS

SF   NFCW             wk7  SEA          13          6 HOME WIN

SF   NFCW             wk8  @AZ          24          3 AWAY WIN

SF   NFCW             wk9  STL          24         24 HOME TIE

SF   NFCW             wk10 CHI          32          7 HOME WIN

SF   NFCW             wk11 @NO          31         21 AWAY WIN

SF   NFCW             wk12 @STL         13         16 AWAY LOSS

SF   NFCW             wk13 MIA          27         13 HOME WIN

SF   NFCW             wk14 @NE          41         34 AWAY WIN

SF   NFCW             wk15 @SEA         13         42 AWAY LOSS

SF   NFCW             wk16 AZ           27         13 HOME WIN

SEA  NFCW             wk1  @AZ          16         20 AWAY LOSS

SEA  NFCW             wk2  DAL          27          7 HOME WIN

34 rows selected.