Forum Stats

  • 3,781,559 Users
  • 2,254,530 Discussions
  • 7,879,753 Comments

Discussions

Can we write case statements inside the insert clause? I need to insert 5 records from the lookup

Albert Chao
Albert Chao Member Posts: 160 Green Ribbon
CREATE TABLE new_details (
    e_id NUMBER(10),
    CONSTRAINT pk_new_details PRIMARY KEY ( e_id )
);

insert into new_details values(11);
insert into new_details values(22);

CREATE SEQUENCE lookup_sq;

CREATE TABLE lookup (
    ref_id      NUMBER(10),
    ref_typ     VARCHAR2(30),
    ref_typ_id  NUMBER(20),
    CONSTRAINT pk_lookup PRIMARY KEY ( ref_id )
);

insert into lookup values(lookup_sq.nextval,'status',33);
insert into lookup values(lookup_sq.nextval,'status',34);
insert into lookup values(lookup_sq.nextval,'status',35);
insert into lookup values(lookup_sq.nextval,'status',36);
insert into lookup values(lookup_sq.nextval,'status',37);

create sequence limestone_sq;
CREATE TABLE limestone (
    limestone_id      NUMBER(10),
    e_id              NUMBER(10),
    ref_type_id       NUMBER(20),
    limestone_prg_id  NUMBER(10),
    sort_order        NUMBER(10),
    CONSTRAINT pk_limestone PRIMARY KEY ( limestone_id ),
    CONSTRAINT fk_pk_limestone_e_id FOREIGN KEY ( e_id )
        REFERENCES new_details ( e_id )
);


I need to insert records into the limestone table but I got stuck about whether we can write case statements into the insert clause or not. And also how will I generate sort_order numbers.

insert into limestone(limestone_id,e_id,ref_type_id,limestone_prg_id,sort_order)
values(limestone_sq.nextval,select e_id from new_details,select ref_type_id from lookup where ref_typ='status',
limestone_prg_id = case when ref_typ_id = 33 then 3 
                        when ref_typ_id = 34 then 3 
                        when ref_typ_id = 35 then 3 
                        when ref_typ_id = 36 then 2 
                        when ref_typ_id = 37 then 1 
                        end ,
                        need to print sorting that starts from 1 until 5 for individual e_id);

Expected output :



Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,384 Red Diamond
    edited Nov 23, 2021 3:51PM Accepted Answer

    Hi, @Albert Chao

    I got stuck about whether we can write case statements into the insert clause or not. 
    

    No, it's impossible to use a CASE statement in an INSERT statement. You can, however, use a CASE expression anywhere other kinds of expressions are allowed, including in INSERT statements.

    And also how will I generate sort_order numbers.
    

    It depends on how you want to assign them. The INSERT statement below assigns consecutive integers (1, 2, 3, ...) in order by ref_typ_id, with a separate set (starting with 1) for each distinct e_id.

    INSERT INTO limestone (limestone_id, e_id, ref_type_id, limestone_prg_id, sort_order)
    SELECT    limestone_sq.NEXTVAL
    ,	  n.e_id
    ,	  l.ref_typ_id
    ,	  CASE
    	        WHEN l.ref_typ_id IN (33, 34, 35)  THEN 3
    		WHEN l.ref_typ_id = 36  	   THEN 2
    		WHEN l.ref_typ_id = 37  	   THEN 1
    	  END
    ,	  ROW_NUMBER () OVER ( PARTITION BY n.e_id
    	  	    	       ORDER BY   l.ref_typ_id
    			     )
    FROM	   new_details n
    CROSS JOIN lookup      l
    ;
    

    The VALUES keyword is only used in INSERT statements where you want to insert exactly one row, and you don't need to get any of the values from any table.

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,384 Red Diamond
    edited Nov 23, 2021 3:51PM Accepted Answer

    Hi, @Albert Chao

    I got stuck about whether we can write case statements into the insert clause or not. 
    

    No, it's impossible to use a CASE statement in an INSERT statement. You can, however, use a CASE expression anywhere other kinds of expressions are allowed, including in INSERT statements.

    And also how will I generate sort_order numbers.
    

    It depends on how you want to assign them. The INSERT statement below assigns consecutive integers (1, 2, 3, ...) in order by ref_typ_id, with a separate set (starting with 1) for each distinct e_id.

    INSERT INTO limestone (limestone_id, e_id, ref_type_id, limestone_prg_id, sort_order)
    SELECT    limestone_sq.NEXTVAL
    ,	  n.e_id
    ,	  l.ref_typ_id
    ,	  CASE
    	        WHEN l.ref_typ_id IN (33, 34, 35)  THEN 3
    		WHEN l.ref_typ_id = 36  	   THEN 2
    		WHEN l.ref_typ_id = 37  	   THEN 1
    	  END
    ,	  ROW_NUMBER () OVER ( PARTITION BY n.e_id
    	  	    	       ORDER BY   l.ref_typ_id
    			     )
    FROM	   new_details n
    CROSS JOIN lookup      l
    ;
    

    The VALUES keyword is only used in INSERT statements where you want to insert exactly one row, and you don't need to get any of the values from any table.