Home » SQL & PL/SQL » SQL & PL/SQL » Oracle case INSERT statement (19.2)
Oracle case INSERT statement [message #685546] Fri, 04 February 2022 13:00 Go to next message
Unclefool
Messages: 82
Registered: August 2021
Member
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;

Re: Oracle case INSERT statement [message #685547 is a reply to message #685546] Fri, 04 February 2022 13:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It is not an INSERT VALUES but an INSERT SELECT you have to use:
MIKJ3DB1> insert into T21 (num, state)
  2  select LEVEL,
  3       CASE  round(dbms_random.value(1,50))
  4      WHEN 1 THEN 'Alabama'
  5      WHEN 2 THEN 'Alaska'
  6      WHEN 3 THEN 'Arizona'
  7      WHEN 4 THEN 'Arkansas'
  8      WHEN 5 THEN 'California'
  9      WHEN 6 THEN 'Colorado'
 10      WHEN 7 THEN 'Connecticut'
 11      WHEN 8 THEN 'Delaware'
 12      WHEN 9 THEN 'Florida'
 13      WHEN 10 THEN 'Georgia'
 14      WHEN 11 THEN 'Hawaii'
 15      WHEN 12 THEN 'Idaho'
 16      WHEN 13 THEN 'Illinois'
 17      WHEN 14 THEN 'Indiana'
 18      WHEN 15 THEN 'Iowa'
 19      WHEN 16 THEN 'Kansas'
 20      WHEN 17 THEN 'Kentucky'
 21      WHEN 18 THEN 'Louisiana'
 22      WHEN 19 THEN 'Maine'
 23      WHEN 20 THEN 'Maryland'
 24      WHEN 21 THEN 'Massachusetts'
 25      WHEN 22 THEN 'Michigan'
 26      WHEN 23 THEN 'Minnesota'
 27      WHEN 24 THEN 'Mississippi'
 28      WHEN 25 THEN 'Missouri'
 29      WHEN 26 THEN 'Montana'
 30      WHEN 27 THEN 'Nebraska'
 31      WHEN 28 THEN 'Nevada'
 32      WHEN 29 THEN 'New Hampshire'
 33      WHEN 30 THEN 'New Jersey'
 34      WHEN 31 THEN 'New Mexico'
 35      WHEN 32 THEN 'New York'
 36      WHEN 33 THEN 'North Carolina'
 37      WHEN 34 THEN 'North Dakota'
 38      WHEN 35 THEN 'Ohio'
 39      WHEN 36 THEN 'Oklahoma'
 40      WHEN 37 THEN 'Oregon'
 41      WHEN 38 THEN 'Pennsylvania'
 42      WHEN 39 THEN 'Rhode Island'
 43      WHEN 40 THEN 'South Carolina'
 44      WHEN 41 THEN 'South Dakota'
 45      WHEN 42 THEN 'Tennessee'
 46      WHEN 43 THEN 'Texas'
 47      WHEN 44 THEN 'Utah'
 48      WHEN 45 THEN 'Vermont'
 49      WHEN 46 THEN 'Virginia'
 50      WHEN 47 THEN 'Washington'
 51      WHEN 48 THEN 'West Virginia'
 52      WHEN 49 THEN 'Wisconsin'
 53      WHEN 50 THEN 'Wyoming'
 54  END AS state
 55  from dual
 56  CONNECT BY LEVEL<=100;

100 rows created.
Oracle case INSERT statement [message #685548 is a reply to message #685546] Fri, 04 February 2022 15:17 Go to previous messageGo to next message
Unclefool
Messages: 82
Registered: August 2021
Member
Thanks for responding I figured that out and did a rewrite but I can't understand why my version comes up with some empty states

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 Pennsylvania
2 Kentucky
3 Delaware
4 Iowa
5
6
7
8 Maine
9 California
10
11
12
13
14 California
15 New Hampshire
16 North Dakota
17
18 Vermont
19
20 Hawaii
21 Delaware
22
23 Oregon
24
25
26 New Mexico
27
28
29 Vermont
30
31 Louisiana
32
33
34 Massachusetts
35 Tennessee
36 Arizona
37 Delaware
38 Kentucky
39
40 Connecticut
41
42
43 Iowa
44 Alabama
45 Idaho
46
47
48 Indiana
49 Alaska
50 New Jersey
51 Nebraska
52
53 Iowa
54 Massachusetts
55 Arizona
56
57 Maryland
58 New Jersey
59
60 West Virginia
61 Kansas
62 Kansas
63 Maine
64 Vermont
65
66 Alaska
67 California
68
69 Missouri
70
71 Michigan
72
73 Pennsylvania
74 New York
75 Wyoming
76 Pennsylvania
77
78
79
80 Nebraska
81
82 Michigan
83
84
85 Nebraska
86
87 Kansas
88 New Hampshire
89 South Carolina
90 California
91 Alaska
92 South Carolina
93 Pennsylvania
94 Colorado
95
96 Pennsylvania
98 Utah
99 Illinois
100

100 rows selected.

Re: Oracle case INSERT statement [message #685549 is a reply to message #685546] Fri, 04 February 2022 16:03 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
So, I'm curious. What does this accomplish that is not accomplished by a simple series of INSERT statements?

SQL> show user
USER is "SCOTT"
SQL> -- -------- create the tables
SQL> create table T21 (state_id number,
  2                    state_name varchar2(20)
  3                   )
  4  ;

Table created.

SQL> -- -------- load tables
SQL> insert into T21 values (1,'Alabama');

1 row created.

SQL> insert into T21 values (2,'Alaska');

1 row created.

SQL> -- -------- Do the query
SQL> select * from T21;

  STATE_ID STATE_NAME
---------- --------------------
         1 Alabama
         2 Alaska

2 rows selected.

SQL> -- -------- clean up
SQL> drop table t21 purge;

Table dropped.
Re: Oracle case INSERT statement [message #685550 is a reply to message #685546] Fri, 04 February 2022 20:03 Go to previous messageGo to next message
Unclefool
Messages: 82
Registered: August 2021
Member
I was looking for a fast and easy way to generate data. In some of my environments we have aitgenerate list PARTITIONs enabled and users are complaining about the PARTITION names.

My goal was to generate date than write a procedure to rename the PARTITIONs ie P_IOWA, P_TEXAS... ALOR IF the high_value is a number P_10,P_11.. This is a setup for the real task
Re: Oracle case INSERT statement [message #685551 is a reply to message #685548] Sat, 05 February 2022 00:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Unclefool wrote on Fri, 04 February 2022 22:17
Thanks for responding I figured that out and did a rewrite but I can't understand why my version comes up with some empty states
...
Not my case:
MIKJ3DB1> select * from t21;
   SEQ_NUM        NUM STATE
---------- ---------- --------------------
         1          1 Iowa
         8          8 Iowa
        17         17 Iowa
        80         80 Iowa
         2          2 Utah
        51         51 Utah
         3          3 Ohio
         6          6 Ohio
        39         39 Ohio
        62         62 Ohio
        83         83 Ohio
        94         94 Ohio
         4          4 South Carolina
         5          5 Rhode Island
        89         89 Rhode Island
        90         90 Rhode Island
         7          7 Delaware
         9          9 Wisconsin
        10         10 Nevada
        31         31 Nevada
        54         54 Nevada
        93         93 Nevada
        11         11 Vermont
        30         30 Vermont
        77         77 Vermont
        12         12 South Dakota
        47         47 South Dakota
        69         69 South Dakota
        97         97 South Dakota
        13         13 Maryland
        28         28 Maryland
        55         55 Maryland
        65         65 Maryland
        75         75 Maryland
        14         14 West Virginia
        19         19 West Virginia
        87         87 West Virginia
        15         15 Indiana
        27         27 Indiana
        16         16 New York
        18         18 Connecticut
        37         37 Connecticut
        40         40 Connecticut
        50         50 Connecticut
        82         82 Connecticut
        20         20 Minnesota
        78         78 Minnesota
        85         85 Minnesota
        99         99 Minnesota
        21         21 Washington
        25         25 Washington
        44         44 Washington
        58         58 Washington
        70         70 Washington
        86         86 Washington
        22         22 Arkansas
        64         64 Arkansas
        23         23 New Mexico
        74         74 New Mexico
        24         24 Colorado
        72         72 Colorado
        26         26 Maine
        29         29 Pennsylvania
        71         71 Pennsylvania
        32         32 Oregon
        53         53 Oregon
        33         33 California
        34         34 Tennessee
        41         41 Tennessee
        46         46 Tennessee
        67         67 Tennessee
        35         35 New Jersey
        61         61 New Jersey
        36         36 Alabama
        63         63 Alabama
        38         38 Texas
        42         42 Kentucky
        88         88 Kentucky
        43         43 North Carolina
        45         45 Louisiana
        49         49 Louisiana
        48         48 Kansas
        52         52 Kansas
        76         76 Kansas
        56         56 Idaho
        96         96 Idaho
        57         57 Illinois
        59         59 Georgia
        95         95 Georgia
       100        100 Georgia
        60         60 Nebraska
        91         91 Nebraska
        66         66 Missouri
        81         81 Missouri
        98         98 Missouri
        68         68 Montana
        73         73 Michigan
        79         79 Michigan
        84         84 Mississippi
        92         92 Oklahoma

100 rows selected.

MIKJ3DB1> @v

Oracle version: 19.13.0.0.211019 EE
Post your Oracle version, with 4 decimals (query bannerèfull from v$version).
Please read How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Re: Oracle case INSERT statement [message #685554 is a reply to message #685550] Sat, 05 February 2022 09:14 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Unclefool wrote on Fri, 04 February 2022 20:03
I was looking for a fast and easy way to generate data. In some of my environments we have aitgenerate list PARTITIONs enabled and users are complaining about the PARTITION names.
What do users care about partition names?


Quote:
My goal was to generate date than write a procedure to rename the PARTITIONs ie P_IOWA, P_TEXAS... ALOR IF the high_value is a number P_10,P_11.. This is a setup for the real task
Then you should address the real requirement . . .

Previous Topic: Print value in table columns dynamically
Next Topic: record count between one hour
Goto Forum:
  


Current Time: Thu Mar 28 17:36:08 CDT 2024