I am getting an error: ORA-01789: query block has incorrect number of result columns
when trying to create a table from data in 2 other tables. Please help, is this just a syntax error or am I combining the tables in the wrong way?
CREATE TABLE EMPDATA(ID, NAME, SALARY, DEPTNAME)
AS
SELECT e.employee_id, (e.first_name || e.last_name), e.salary
FROM employees e
UNION
SELECT d.department_name
FROM departments d;
I think you want JOIN instead of UNION:
CREATE TABLE EMPDATA(ID, NAME, SALARY, DEPTNAME)
AS
SELECT e.employee_id, (e.first_name || e.last_name), e.salary, d.department_name
FROM employees e
JOIN departments d on(d.department_id = e.department_id);
The number of coumns while using UNION should be same in the SELECT statement
You need to join to set the department and not union
CREATE TABLE EMPDATA(ID, NAME, SALARY, DEPTNAME)
AS
SELECT e.employee_id, (e.first_name || e.last_name), e.salary, d.department_name
FROM employees e
JOIN departments d on d.id = e.department_id
You might need to adjust the column names of the join condition since you did not mention the relation.
Your select query returns three columns, for the table you want four. union does union the results of queries, but they have to have same number and types of columns. Your second query has one column, first - three.
When you create a table from a subquery, the resulting columns must be of the same type and number of columns as the subquery.
It is not worth having an EMPLOYEE_ID column of type NUMBER in the DDL and having the column EMPLOYEE_ID of type DATE for example.
Maybe the column exists in the DDL and the subquery DOES NOT EXIST. in your example you try to have the column DEPTNAME in the DDL, but in the subquery that column does not exist in the table EMPLOYEES, If you want to have the column DEPTNAME, you have to do a JOIN with the table DEPARTMENTS.
Remaining as follows:
CREATE TABLE EMPDATA (ID, NAME, SALARY, DEPTNAME) AS
SELECT e.employee_id, (e.first_name || e.last_name), e.salary, d.department_name
FROM employees e,
departments d
WHERE 1 = 1
AND d.department_id = e.department_id;
In my BLOG I have an article that talks about the DDL CREATE TABLE statement in ORACLE SQL, I'll share it for you.
There I talk about a little more things, like creating primary, foreign, insert, etc. klaves.
jointo set the department and notunion