Oracle INSERT with a CASE statement

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;

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>