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

Answer

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;

Advertisement

Answer