I am creating a "Position" table with which to use as a lookup. Each time I look up a value in the position table, I want to use that value as I create/insert sample data into a statistics table. My insert works but for the Position field, I only get the value "First Base" inserted into each row 75 times. Can someone show me what I am doing wrong so I can get the other positions to get inserted (or at least some of them randomly of course)? The line in question is marked with ***.
CREATE TABLE Position
(
PositionId INT NOT NULL
, Position VARCHAR2(30) NOT NULL
);
Insert into POSITION (POSITIONID,POSITION) values (2,'Catcher');
Insert into POSITION (POSITIONID,POSITION) values (3,'First Base');
Insert into POSITION (POSITIONID,POSITION) values (4,'Second Base');
Insert into POSITION (POSITIONID,POSITION) values (5,'Third Base');
Insert into POSITION (POSITIONID,POSITION) values (6,'Shortstop');
Insert into POSITION (POSITIONID,POSITION) values (7,'Left Field');
Insert into POSITION (POSITIONID,POSITION) values (8,'Center Field');
Insert into POSITION (POSITIONID,POSITION) values (9,'Right Field');
CREATE TABLE SEASON_STATISTICS
(
SEASON_STATISTICSID INT NOT NULL
, EMPLOYEEID INT NOT NULL
, GAMES INT NOT NULL
, YEAR INT NOT NULL
, ATBATS INT DEFAULT 0
, HITS INT DEFAULT 0
, HR INT DEFAULT 0
, RBI INT DEFAULT 0
, AVG NUMBER DEFAULT 0
, POSITION VARCHAR2(20)
, TEAMID INT
, LEAGUE VARCHAR2(20)
, ERA NUMBER
, HITSALLOWED INT
, INNINGSPITCHED NUMBER
, Walks Number
, "DATE_STATS_MODIFIED" DATE
);
CREATE SEQUENCE SEASON_STATS_SEQ MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL;
INSERT INTO SEASON_STATISTICS (Season_StatisticsId, EMPLOYEEID, GAMES, YEAR, ATBATS, HITS, HR, RBI, AVG, POSITION, TEAMID, LEAGUE)
SELECT SEASON_STATS_SEQ.nextval,
ROUND(dbms_random.value(1, 9)), -- EmployeeId
ROUND(dbms_random.value(1, 56)), -- Games
'2022',
ROUND(dbms_random.value(50, 175)), -- At Bats
ROUND(dbms_random.value(15, 45)), -- Hits
ROUND(dbms_random.value(1, 17)), -- HR
ROUND(dbms_random.value(1, 64)), -- RBI
ROUND(dbms_random.value(100, 350)) / 1000, -- Avg
***(SELECT Position FROM Position WHERE PositionId = (SELECT ROUND(dbms_random.value(2, 9)) FROM Dual)),
ROUND(dbms_random.value(1, 10)),
'NL'
FROM DUAL
connect by level <= 75;
CodePudding user response：
The result of your subquery is being cached and reused. Ideally, you would not store the lookup value of your POSITION in your SEASON_STATISTICS table - you would store the POSITIONID as a foreign key and join to the POSITION table when you needed the text value. This would allow you call the DBMS_RANDOM function directly - forcing the re-execution and avoiding cached values.
Try something more like this:
CREATE TABLE Position
(
PositionId INT PRIMARY KEY NOT NULL
, Position VARCHAR2(30) NOT NULL
);
Insert into POSITION (POSITIONID,POSITION) values (2,'Catcher');
Insert into POSITION (POSITIONID,POSITION) values (3,'First Base');
Insert into POSITION (POSITIONID,POSITION) values (4,'Second Base');
Insert into POSITION (POSITIONID,POSITION) values (5,'Third Base');
Insert into POSITION (POSITIONID,POSITION) values (6,'Shortstop');
Insert into POSITION (POSITIONID,POSITION) values (7,'Left Field');
Insert into POSITION (POSITIONID,POSITION) values (8,'Center Field');
Insert into POSITION (POSITIONID,POSITION) values (9,'Right Field');
CREATE TABLE SEASON_STATISTICS
(
SEASON_STATISTICSID INT NOT NULL
, EMPLOYEEID INT NOT NULL
, GAMES INT NOT NULL
, YEAR INT NOT NULL
, ATBATS INT DEFAULT 0
, HITS INT DEFAULT 0
, HR INT DEFAULT 0
, RBI INT DEFAULT 0
, AVG NUMBER DEFAULT 0
, POSITIONID INT REFERENCES position(positionid)
, TEAMID INT
, LEAGUE VARCHAR2(20)
, ERA NUMBER
, HITSALLOWED INT
, INNINGSPITCHED NUMBER
, Walks Number
, DATE_STATS_MODIFIED DATE
);
CREATE SEQUENCE SEASON_STATS_SEQ MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL;
INSERT INTO SEASON_STATISTICS (Season_StatisticsId, EMPLOYEEID, GAMES, YEAR, ATBATS, HITS, HR, RBI, AVG, POSITIONID, TEAMID, LEAGUE)
SELECT SEASON_STATS_SEQ.nextval,
ROUND(dbms_random.value(1, 9)), -- EmployeeId
ROUND(dbms_random.value(1, 56)), -- Games
'2022',
ROUND(dbms_random.value(50, 175)), -- At Bats
ROUND(dbms_random.value(15, 45)), -- Hits
ROUND(dbms_random.value(1, 17)), -- HR
ROUND(dbms_random.value(1, 64)), -- RBI
ROUND(dbms_random.value(100, 350)) / 1000, -- Avg
ROUND(dbms_random.value(2, 9)), -- Position
ROUND(dbms_random.value(1, 10)),
'NL'
FROM DUAL
connect by level <= 75;
commit;
You should see output like this:
Table POSITION created.
1 row inserted.
1 row inserted.
1 row inserted.
1 row inserted.
1 row inserted.
1 row inserted.
1 row inserted.
1 row inserted.
Table SEASON_STATISTICS created.
Sequence SEASON_STATS_SEQ created.
75 rows inserted.
Commit complete.
Then query your tables together, like this:
select
s.SEASON_STATISTICSID
, s.EMPLOYEEID
, s.GAMES
, s.YEAR
, s.ATBATS
, s.HITS
, s.HR
, s.RBI
, s.AVG
, p.POSITION
, s.TEAMID
, s.LEAGUE
, s.ERA
, s.HITSALLOWED
, s.INNINGSPITCHED
, s.Walks
, s.DATE_STATS_MODIFIED
from season_statistics s
join position p on (p.positionid = s.positionid);
and you should see this:
SEASON_STATISTICSID EMPLOYEEID GAMES YEAR ATBATS HITS HR RBI AVG POSITION TEAMID LEAGUE ERA HITSALLOWED INNINGSPITCHED WALKS DATE_STAT
------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------------------ ---------- -------------------- ---------- ----------- -------------- ---------- ---------
1 5 26 2022 78 36 12 18 .111 Center Field 4 NL
2 7 25 2022 76 43 6 42 .263 First Base 4 NL
3 9 18 2022 72 34 8 43 .281 Second Base 9 NL
4 2 20 2022 165 18 7 24 .128 Shortstop 8 NL
5 6 29 2022 164 29 3 58 .127 Right Field 4 NL
6 4 41 2022 100 37 10 28 .277 Left Field 9 NL
7 2 15 2022 140 20 14 5 .147 First Base 3 NL
8 7 46 2022 171 44 10 51 .241 First Base 4 NL
9 3 5 2022 80 32 16 12 .116 Third Base 9 NL
10 4 46 2022 57 38 5 42 .12 Second Base 4 NL