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

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 :

CodePudding user response：

Use INSERT ... SELECT and CROSS JOIN the two tables:

insert into limestone(
  limestone_id,
  e_id,
  ref_type_id,
  limestone_prg_id,
  sort_order
)
SELECT limestone_sq.nextval,
       e_id,
       ref_typ_id,
       case ref_typ_id
       when 33 then 3 
       when 34 then 3 
       when 35 then 3 
       when 36 then 2 
       when 37 then 1
       end,
       ROW_NUMBER() OVER (PARTITION BY e_id ORDER BY ref_typ_id)
from   new_details CROSS JOIN lookup
where  ref_typ='status';

db<>fiddle here