CODEWITHSUNDEEP
sqloracle-databaseoracle11gsql-insert
Random guy
Random guy

Reputation: 923

INSERT into statement not working in oracle when using INSERT INTO .. SELECT * FROM

I have vh_emp table whose table structure is(This is empty table):

enter image description here

I am taking data from employee table as source table where I wrote some query to match with vh_emp table:

SELECT e.emp_id       EMP_ID, 
       e.emp_name     EMP_NAME, 
       e.dob          DOB, 
       e.join_date    JOIN_DATE, 
       CASE 
         WHEN e.leave_date IS NULL THEN Trunc(sysdate) 
         ELSE e.leave_date 
       END            AS LEAVE_DATE, 
       e.salary / 100 SALARY, 
       d.dept_name    DEPT_NAME, 
       one.addr_name  ADDRESS_ONE, 
       two.addr_name  ADDRESS_TWO, 
       b.emp_name     AS MANAGER_NAME 
FROM   offc.employee e 
       LEFT JOIN offc.department d 
              ON e.dept_id = d.dept_id 
       LEFT JOIN offc.add_line_one one 
              ON e.line1 = one.addr_id_one 
       LEFT JOIN offc.add_line_two two 
              ON e.line2 = two.addr_id_two 
       LEFT OUTER JOIN (SELECT * 
                        FROM   offc.employee) b 
                    ON e.manager_id = b.emp_id;

The query is running fine and the output is coming fine from this query:

enter image description here

So,I wanted to insert these data in vh_emp tables using :

INSERT INTO offc.vh_emp 
            (emp_id, 
             emp_name, 
             dob, 
             join_date, 
             leave_date, 
             address_one, 
             address_two, 
             salary, 
             manager_name, 
             dept_name) 
SELECT e.emp_id       EMP_ID, 
       e.emp_name     EMP_NAME, 
       e.dob          DOB, 
       e.join_date    JOIN_DATE, 
       CASE 
         WHEN e.leave_date IS NULL THEN Trunc(sysdate) 
         ELSE e.leave_date 
       END            AS LEAVE_DATE, 
       e.salary / 100 SALARY, 
       d.dept_name    DEPT_NAME, 
       one.addr_name  ADDRESS_ONE, 
       two.addr_name  ADDRESS_TWO, 
       b.emp_name     AS MANAGER_NAME 
FROM   offc.employee e 
       LEFT JOIN offc.department d 
              ON e.dept_id = d.dept_id 
       LEFT JOIN offc.add_line_one one 
              ON e.line1 = one.addr_id_one 
       LEFT JOIN offc.add_line_two two 
              ON e.line2 = two.addr_id_two 
       LEFT OUTER JOIN (SELECT * 
                        FROM   offc.employee) b 
                    ON e.manager_id = b.emp_id;

But,I executed this query I got error as:

ORA-01722: invalid number

The structure of table employee is: enter image description here

why is this error coming? I reviewed and saw that datatype is same for the column and value of select statement.This error should not appear as column datatype is matching.

Upvotes: 0

Views: 891

Answers (1)

Popeye
Popeye

Reputation: 35930

Order of your insert clause column name and select clause column name should match.

Please match the positions of them as following:

INSERT INTO offc.vh_emp 
            (emp_id, 
             emp_name, 
             dob, 
             join_date, 
             leave_date, 
             Salary,  -- this
             dept_name, -- this
             address_one, 
             address_two,
             manager_name 
             ) 
SELECT e.emp_id       EMP_ID, 
       e.emp_name     EMP_NAME, 
       e.dob          DOB, 
       e.join_date    JOIN_DATE, 
       CASE 
         WHEN e.leave_date IS NULL THEN Trunc(sysdate) 
         ELSE e.leave_date 
       END            AS LEAVE_DATE, 
       e.salary / 100 SALARY, 
       d.dept_name    DEPT_NAME, 
       one.addr_name  ADDRESS_ONE, 
       two.addr_name  ADDRESS_TWO, 
       b.emp_name     AS MANAGER_NAME 
FROM   offc.employee e 
       LEFT JOIN offc.department d 
              ON e.dept_id = d.dept_id 
       LEFT JOIN offc.add_line_one one 
              ON e.line1 = one.addr_id_one 
       LEFT JOIN offc.add_line_two two 
              ON e.line2 = two.addr_id_two 
       LEFT OUTER JOIN (SELECT * 
                        FROM   offc.employee) b 
                    ON e.manager_id = b.emp_id;

Cheers!!

Upvotes: 2

Related Questions