CODEWITHSUNDEEP
sqloracle-databaseddl
user3037147
user3037147

Reputation: 63

SQL create table column AS SELECT FROM OTHER TABLE

My problem is I cannot create proper DDL statement 'create table' with column based on 2 columns from other table.

The final table should looks like after:

CREATE TABLE PRACOWNICY_ZESPOLY AS
  SELECT 12*PLACA_POD + NVL(PLACA_DOD,0) AS ROCZNA_PLACA FROM PRAC;

ALTER TABLE PRACOWNICY_ZESPOLY 
  ADD (
      NAZWISKO VARCHAR(20),
      POSADA VARCHAR(20),
      ZESPOL NUMBER(4),
      ADRES_PRACY VARCHAR(20) );

I was trying something like this:

CREATE TABLE PRACOWNICY_ZESPOLY (
  NAZWISKO VARCHAR(20),
  POSADA VARCHAR(20),
  ZESPOL NUMBER(4),
  ADRES_PRACY VARCHAR(20),
  ROCZNA_PLACA NUMBER(6,2) AS (SELECT 12*PLACA_POD + NVL(PLACA_DOD,0) FROM PRAC));

Result:

SQL Error: ORA-00936: missing expression

CREATE TABLE PRACOWNICY_ZESPOLY (
  NAZWISKO VARCHAR(20),
  POSADA VARCHAR(20),
  ZESPOL NUMBER(4),
  ADRES_PRACY VARCHAR(20))
  AS SELECT 12*PLACA_POD + NVL(PLACA_DOD,0) FROM PRAC;

Result:

SQL Error: ORA-01773: may not specify column datatypes in this CREATE TABLE

CREATE TABLE PRACOWNICY_ZESPOLY AS
  SELECT 12*PLACA_POD + NVL(PLACA_DOD,0) AS ROCZNA_PLACA FROM PRAC, 
  (NAZWISKO VARCHAR(20),
  POSADA VARCHAR(20),
  ZESPOL NUMBER(4),
  ADRES_PRACY VARCHAR(20));

Result:

SQL Error: ORA-00907: missing right parenthesis

Upvotes: 1

Views: 4367

Answers (3)

yu yang Jian
yu yang Jian

Reputation: 7165

I have found that to solve

Error: ORA-01773: may not specify column datatypes in this CREATE TABLE

this article work:

How to deal with ORA-01773 from Oracle 9i ? Remove column datatypes. The datatypes of the SELECT list expressions are automatically used as the column datatypes.

Upvotes: 0

Alessandro C
Alessandro C

Reputation: 3560

I have found a working solution in this question.

You need to combine get_ddl with CTAS syntax.

Upvotes: 1

user330315
user330315

Reputation:

You need a create and an insert statement:

CREATE TABLE PRACOWNICY_ZESPOLY 
(
  ROCZNA_PLACA  number, 
  NAZWISKO      VARCHAR(20),
  POSADA        VARCHAR(20),
  ZESPOL        NUMBER(4),
  ADRES_PRACY   VARCHAR(20)
);

insert into PRACOWNICY_ZESPOLY (ROCZNA_PLACA) 
SELECT 12 * PLACA_POD + NVL(PLACA_DOD,0) 
FROM PRAC;

Upvotes: 2

Related Questions