I have the following table declaration and I’m trying to use a case statement to INSERT some rows and I’m getting the following error
ORA-00976: Specified pseudocolumn or operator not allowed here.
Can someone please let me know how to rectify the INSERT code.
Thanks in advance to all who answer.
CREATE TABLE T21
(
seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
num NUMBER(*,0) NOT NULL,
state VARCHAR2(20) NOT NULL
)
PARTITION BY LIST (state) AUTOMATIC
(PARTITION P_CALIFORNIA VALUES ('CALIFORNIA')
);
insert into T21 (num, state) VALUES (LEVEL,
CASE round(dbms_random.value(1,50))
WHEN 1 THEN 'Alabama'
WHEN 2 THEN 'Alaska'
WHEN 3 THEN 'Arizona'
WHEN 4 THEN 'Arkansas'
WHEN 5 THEN 'California'
WHEN 6 THEN 'Colorado'
WHEN 7 THEN 'Connecticut'
WHEN 8 THEN 'Delaware'
WHEN 9 THEN 'Florida'
WHEN 10 THEN 'Georgia'
WHEN 11 THEN 'Hawaii'
WHEN 12 THEN 'Idaho'
WHEN 13 THEN 'Illinois'
WHEN 14 THEN 'Indiana'
WHEN 15 THEN 'Iowa'
WHEN 16 THEN 'Kansas'
WHEN 17 THEN 'Kentucky'
WHEN 18 THEN 'Louisiana'
WHEN 19 THEN 'Maine'
WHEN 20 THEN 'Maryland'
WHEN 21 THEN 'Massachusetts'
WHEN 22 THEN 'Michigan'
WHEN 23 THEN 'Minnesota'
WHEN 24 THEN 'Mississippi'
WHEN 25 THEN 'Missouri'
WHEN 26 THEN 'Montana'
WHEN 27 THEN 'Nebraska'
WHEN 28 THEN 'Nevada'
WHEN 29 THEN 'New Hampshire'
WHEN 30 THEN 'New Jersey'
WHEN 31 THEN 'New Mexico'
WHEN 32 THEN 'New York'
WHEN 33 THEN 'North Carolina'
WHEN 34 THEN 'North Dakota'
WHEN 35 THEN 'Ohio'
WHEN 36 THEN 'Oklahoma'
WHEN 37 THEN 'Oregon'
WHEN 38 THEN 'Pennsylvania'
WHEN 39 THEN 'Rhode Island'
WHEN 40 THEN 'South Carolina'
WHEN 41 THEN 'South Dakota'
WHEN 42 THEN 'Tennessee'
WHEN 43 THEN 'Texas'
WHEN 44 THEN 'Utah'
WHEN 45 THEN 'Vermont'
WHEN 46 THEN 'Virginia'
WHEN 47 THEN 'Washington'
WHEN 48 THEN 'West Virginia'
WHEN 49 THEN 'Wisconsin'
WHEN 50 THEN 'Wyoming'
END AS state)
CONNECT BY LEVEL<=100;
Advertisement
Answer
level pseudocolumn goes with select (not just values).
I modified your code a little bit (I’m on 11gXE, it doesn’t allow partitions nor identity columns – you can leave both of those if your database supports them). Also, NOT NULL constraint should be removed for the state column.
SQL> create table t21 2 (seq_num number, 3 num number(*,0), 4 state varchar2(20) 5 ); Table created.
Insert:
SQL> insert into t21 (num, state) 2 select 3 level, 4 case round(dbms_random.value(1,50)) 5 when 1 then 'Alabama' 6 when 2 then 'Alaska' 7 when 3 then 'Arizona' 8 when 4 then 'Arkansas' 9 when 5 then 'California' 10 when 6 then 'Colorado' 11 when 7 then 'Connecticut' 12 when 8 then 'Delaware' 13 when 9 then 'Florida' 14 when 10 then 'Georgia' 15 when 11 then 'Hawaii' 16 when 12 then 'Idaho' 17 when 13 then 'Illinois' 18 when 14 then 'Indiana' 19 when 15 then 'Iowa' 20 when 16 then 'Kansas' 21 when 17 then 'Kentucky' 22 when 18 then 'Louisiana' 23 when 19 then 'Maine' 24 when 20 then 'Maryland' 25 when 21 then 'Massachusetts' 26 when 22 then 'Michigan' 27 when 23 then 'Minnesota' 28 when 24 then 'Mississippi' 29 when 25 then 'Missouri' 30 when 26 then 'Montana' 31 when 27 then 'Nebraska' 32 when 28 then 'Nevada' 33 when 29 then 'New Hampshire' 34 when 30 then 'New Jersey' 35 when 31 then 'New Mexico' 36 when 32 then 'New York' 37 when 33 then 'North Carolina' 38 when 34 then 'North Dakota' 39 when 35 then 'Ohio' 40 when 36 then 'Oklahoma' 41 when 37 then 'Oregon' 42 when 38 then 'Pennsylvania' 43 when 39 then 'Rhode Island' 44 when 40 then 'South Carolina' 45 when 41 then 'South Dakota' 46 when 42 then 'Tennessee' 47 when 43 then 'Texas' 48 when 44 then 'Utah' 49 when 45 then 'Vermont' 50 when 46 then 'Virginia' 51 when 47 then 'Washington' 52 when 48 then 'West Virginia' 53 when 49 then 'Wisconsin' 54 when 50 then 'Wyoming' 55 end 56 from dual 57 connect by level <= 100; 100 rows created. SQL>
Result:
SQL> select * From t21;
SEQ_NUM NUM STATE
---------- ---------- --------------------
1 Indiana
2 Alaska
3 Mississippi
4 New Jersey
5 Missouri
6 Nebraska
7 Washington
8 Washington
9 Alaska
10 Maryland
11 Missouri
12 Florida
13 New Hampshire
14 Colorado
15 South Dakota
16 Tennessee
17 Arizona
18 Alaska
19 Iowa
20 Pennsylvania
21 Indiana
22 Pennsylvania
23 Louisiana
24 Mississippi
25 Rhode Island
26 Virginia
27 New Hampshire
28 Missouri
29 Georgia
30 Missouri
31 Massachusetts
32 Oklahoma
33 Rhode Island
34 South Dakota
35 Georgia
36 Alaska
37 Georgia
38 New Hampshire
39 Michigan
40 Connecticut
41 Idaho
42 Maine
43 South Carolina
44 Hawaii
45 Alaska
46 Washington
47 Iowa
48 New Mexico
49 Oregon
50 Colorado
51 Kansas
52 Wisconsin
53 California
54 Ohio
55 Kentucky
56 Arizona
57 Washington
58 Arizona
59 California
60 North Dakota
61 Alabama
62 Louisiana
63 North Dakota
64 South Carolina
65 Hawaii
66 North Dakota
67 Hawaii
68 Pennsylvania
69 North Dakota
70 Illinois
71 West Virginia
72 Illinois
73 Maryland
74 Arkansas
75 West Virginia
76 Iowa
77 Ohio
78 New York
79 Colorado
80 Montana
81 Alaska
82 Utah
83 Mississippi
84 Indiana
85 Florida
86 Oregon
87 Pennsylvania
88 Oregon
89 Utah
90 Iowa
91 Ohio
92 Georgia
93 Virginia
94 Colorado
95 Virginia
96 Michigan
97 Arizona
98 Wyoming
99 West Virginia
100 Ohio
100 rows selected.
SQL>