Forum Stats

  • 3,752,637 Users
  • 2,250,529 Discussions
  • 7,867,903 Comments

Discussions

Query Help : Duplicate entry

Ricky007
Ricky007 Member Posts: 592 Bronze Badge

Dear Experts,

please help to add duplicate records based on the catergory id against bill num.

create table BILL_STAG

(

S_CATEGRY VARCHAR2(20),

S_BILLNUM VARCHAR2(20)

);



INSERT INTO BILL VALUES('1111','5647859541785');

INSERT INTO BILL VALUES('1111','8524896241773');

INSERT INTO BILL VALUES('2222','3258412985249');


commit;



CREATE table BILL_MAST

(

M_BILL_SEQNUM VARCHAR2(20),

M_BILLNUM VARCHAR2(20),

CHANNEL VARCHAR2(20)

);


CREATE SEQUENCE SEQ_NUM;


Expected Result:


Need to insert 2 records for each category id



Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,926 Red Diamond
    Accepted Answer

    Hi, @Ricky007

    create table BILL_STAG

    (

    S_CATEGRY VARCHAR2(20),

    S_BILLNUM VARCHAR2(20)

    );


    INSERT INTO BILL VALUES('1111','5647859541785');

    Watch out! You're using two different table names there.

    You don't care which m_billnum_seq gets assigned to which new row, do you? (You shouldn't.) In that case, you can simplify the CROSS JOIN approach that Cormaco suggested like this:

    INSERT INTO bill_mast (m_bill_seqnum, m_billnum, channel)  -- Always name the columns
    WITH  all_channels  AS
    (
    	SELECT 'MOB' AS channel   FROM dual   UNION ALL
    	SELECT 'ATM' 	 	  FROM dual
    )
    SELECT     seq_num.NEXTVAL
    ,	   s.s_billnum
    ,	   c.channel
    FROM       bill_stag    s
    CROSS JOIN all_channels c
    ;
    

    You don't need ORDER BY, so you don't need the ord column and you don't need so an extra sub-query.

Answers

  • cormaco
    cormaco Member Posts: 1,653 Bronze Crown
    edited Jul 21, 2021 7:58AM

    Here is one way, I changed the datatype of M_BILL_SEQNUM to number:

    insert into BILL_MAST
    select SEQ_NUM.NEXTVAL, S_BILLNUM, CHANNEL from BILL_STAG cross join (
        select CHANNEL from (select 1 as ord, 'MOB' as CHANNEL from dual union all select 2 as ord, 'ATM' as CHANNEL from dual)
        order by ord);
    
    M_BILL_SEQNUM M_BILLNUM            CHANNEL             
    ------------- -------------------- --------------------
                1 5647859541785        MOB                 
                2 5647859541785        ATM                 
                3 8524896241773        MOB                 
                4 8524896241773        ATM                 
                5 3258412985249        MOB                 
                6 3258412985249        ATM                 
    
    
    
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,926 Red Diamond
    Accepted Answer

    Hi, @Ricky007

    create table BILL_STAG

    (

    S_CATEGRY VARCHAR2(20),

    S_BILLNUM VARCHAR2(20)

    );


    INSERT INTO BILL VALUES('1111','5647859541785');

    Watch out! You're using two different table names there.

    You don't care which m_billnum_seq gets assigned to which new row, do you? (You shouldn't.) In that case, you can simplify the CROSS JOIN approach that Cormaco suggested like this:

    INSERT INTO bill_mast (m_bill_seqnum, m_billnum, channel)  -- Always name the columns
    WITH  all_channels  AS
    (
    	SELECT 'MOB' AS channel   FROM dual   UNION ALL
    	SELECT 'ATM' 	 	  FROM dual
    )
    SELECT     seq_num.NEXTVAL
    ,	   s.s_billnum
    ,	   c.channel
    FROM       bill_stag    s
    CROSS JOIN all_channels c
    ;
    

    You don't need ORDER BY, so you don't need the ord column and you don't need so an extra sub-query.

  • Ricky007
    Ricky007 Member Posts: 592 Bronze Badge

    Thanks again and again frank..