I want to insert into tmp_s table if emp_t.tno=emp.empno and sal1,sal2,sal3 are not null in EMP_T Table .I have written the following query to insert into tmp_s table .Please suggest me this approach is correct or not.Because one of friend told that IF you use stored procedure you have to use 3 times loops(sal1,sal2,sal3 not isnull ) and pass the data.I am new to pl/sql hence i written sql query




SQL> desc tmp_s;
Name Null? Type
----------------------------------------- -------- ------------

EMPNO NUMBER
TRNO NUMBER
SAL NUMBER

SQL> select empno from emp;

EMPNO
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876




SQL> SELECT * FROM EMP_T;

TNO SAL1 SAL2 SAL3
---------- ---------- ---------- ----------
7369 100 200 300
7499 1000 2000 3000
7566 400 500 600
7782 4000 5000 6000



SQL> SELECT EMP.EMPNO,EMP_T.TNO,EMP_T.SAL1 FROM EMP,EMP_T WHERE EMP.EMPNO=EMP_T.
NO AND EMP_T.SAL1 is not null
UNION ALL
SELECT EMP.EMPNO,EMP_T.TNO,EMP_T.SAL2 FROM EMP,EMP_T WHERE EMP.EMPNO=EMP_T.
NO AND EMP_T.SAL2 is not null
UNION ALL
5* SELECT EMP.EMPNO,EMP_T.TNO,EMP_T.SAL3 FROM EMP,EMP_T WHERE EMP.EMPNO=EMP_T.
NO AND EMP_T.SAL1
QL> /

EMPNO TNO SAL1
--------- ---------- ----------
7369 7369 100
7499 7499 1000
7566 7566 400
7782 7782 4000
7369 7369 200
7499 7499 2000
7566 7566 500
7782 7782 5000
7369 7369 300
7499 7499 3000
7566 7566 600

EMPNO TNO SAL1
--------- ---------- ----------
7782 7782 6000

12 rows selected.



INSERT INTO TMP_S (EMPNO,TRNO,SAL)
(SELECT EMP.EMPNO,EMP_T.TNO,EMP_T.SAL1 FROM EMP,EMP_T WHERE EMP.EMPNO=EMP_T.TNO
UNION ALL
SELECT EMP.EMPNO,EMP_T.TNO,EMP_T.SAL2 FROM EMP,EMP_T WHERE EMP.EMPNO=EMP_T.TNO
UNION ALL
SELECT EMP.EMPNO,EMP_T.TNO,EMP_T.SAL3 FROM EMP,EMP_T WHERE EMP.EMPNO=EMP_T.TNO);



Regards
Akil