'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

sqloracleddl


Solution 1:[1]

I have found a working solution in this question.

You need to combine get_ddl with CTAS syntax.

Solution 2:[2]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Alessandro C
Solution 2 yu yang Jian

Related Questions