how to insert records joining two tables with a subquery

Question

Hello i am trying to insert some records joining two tables with a sub query.i would appreciate the help.thanks

Query

insert into rtn_det select * from tmp_rtn_det
where rtn_no in(select a.rtn_no from rtn_det a,rtn_mas b
where a.rtn_no=b.rtn_no
and a.sc_cd=b.sc_cd
and fl_mvh = '2'
and rtn_dt between '01-MAY-15' and '31-MAY-15'
and b.sc_cd='05')
and sc_cd='05'

tables :

SQL> desc rtn_mas
 Name                            Null?    Type
 ------------------------------- -------- ----
 RTN_NO                          NOT NULL VARCHAR2(6)
 SC_CD                           NOT NULL VARCHAR2(2)
 RTN_DT                                   DATE
 RSN_NO                                   VARCHAR2(1)
 BILL_NO                                  VARCHAR2(6)
 FP_ID                                    VARCHAR2(4)
 SALE_CD                         NOT NULL VARCHAR2(2)
 RTN_GR                          NOT NULL NUMBER(9,2)
 RTN_NET                         NOT NULL NUMBER(9,2)
 FL_MVH                                   VARCHAR2(1)
 USER_CD                                  VARCHAR2(2)
 W_DT                                     DATE
 CANCL                                    VARCHAR2(1)
 R_VAT                                    NUMBER(9,2)



SQL> 
SQL> 
SQL> desc rtn_det
 Name                            Null?    Type
 ------------------------------- -------- ----
 RTN_NO                          NOT NULL VARCHAR2(6)
 SC_CD                           NOT NULL VARCHAR2(2)
 TYP_CD                          NOT NULL VARCHAR2(2)
 CAT_CD                          NOT NULL VARCHAR2(2)
 PRD_CD                          NOT NULL VARCHAR2(4)
 RTN_QTY                         NOT NULL NUMBER(7,2)
 RTN_GR                          NOT NULL NUMBER(9,2)
 RTN_NET                         NOT NULL NUMBER(9,2)
 RVAT_AMT                                 NUMBER(8,2)

RTN_MAS and RTN_DET are two tables that i am trying to join and i have also used a sub query.please tell me what i am doing wrong.

some of the data from two tables:

 select * from rtn_det
  2  where rownum < 10
  3  /

RTN_NO SC TY CA PRD_   RTN_QTY    RTN_GR   RTN_NET  RVAT_AMT**
------ -- -- -- ---- --------- --------- --------- ---------
19026  02 09 01 1009         3       291    283.53
19027  02 09 05 1008        10       348     342.1
18347  02 09 08 1038         1        33     32.72
18347  02 09 08 1050         1        65     64.44'





   SQL> select * from rtn_mas
      2  where rownum<10
      3  /

RTN_NO SC RTN_DT    R BILL_N FP_I SA    RTN_GR   RTN_NET F US W_DT      C     R_VAT
------ -- --------- - ------ ---- -- --------- --------- - -- --------- - ---------
158699 10 27-JAN-13 7 863843 2DX6 WM    430.48    419.47 1 62 26-JAN-13        62.5
158700 10 27-JAN-13 7 863843 5498 WM    752.44    732.69 1 62 26-JAN-13          94
158702 10 27-JAN-13 7 868675 2Q91 RC    317.88    315.18 1 62 26-JAN-13       46.26
158703 10 27-JAN-13 7 867416 2Q91 WO   1356.58    1324.2 1 62 26-JAN-13      196.96
158704 10 27-JAN-13 7 867416 5847 WO      1427   1402.62 1 62 26-JAN-13         208
158706 10 27-JAN-13 7 869481 2Q91 WO    808.48    794.28 1 62 26-JAN-13      102.24

regards, Iftekhar

Answer 1

First, is the inner query returning any rows?

select a.rtn_no 
from rtn_det a,rtn_mas b
where a.rtn_no=b.rtn_no
and a.sc_cd=b.sc_cd
and fl_mvh = '2'
and rtn_dt between '01-MAY-15' and '31-MAY-15'
and b.sc_cd='05'

Also, often its best to filter the data by date first, at least in SQL. Putting that where clause first allows an index on date to be used which often massively reduces the rows.

Without the data its difficult to tell, but maybe this query can replace the inner query?

SELECT DISTINCT a.rtn_no 
FROM rtn_det a
INNER JOIN rtn_mas b
ON a.rtn_no=b.rtn_no AND a.sc_cd=b.sc_cd
WHERE rtn_dt BETWEEN '01-MAY-15' AND '31-MAY-15'
AND fl_mvh = '2'
AND b.sc_cd='05'