Implicit Column Types with a CREATE AS SELECT

Let's say we need a column to be a NUMBER(6) in a CREATE TABLE statement. Contents of the column is a fixed number.

Pretend that table DUAL is a VLT which we process and then add a MY_NUM column to it as a NUMBER(6).

CREATE TABLE foobar
AS SELECT
  d.*,
  123456 MY_NUM
FROM dual d;

MY_NUM is a NUMBER. Fine. Makes sense. But this is not what we want.

CREATE TABLE number6
( num6 NUMBER(6) );

INSERT INTO number6 VALUES ( 123456 );

Now, we use that:

CREATE TABLE foobar
AS SELECT
   d.*,
   num6 MY_NUM
FROM dual d, number6;

MY_NUM is a NUMBER(6). Also makes sense.

Now we try:
CREATE TABLE foobar
AS SELECT
  d.*,
  (SELECT num6 FROM number6) MY_NUM
FROM dual;

MY_NUM is a NUMBER and not a NUMBER(6). This does not make sense to me. Should it not inherit the underlaying datatype (from the NUM6 column)?

--

Billy

--

Billy Received on Wed Aug 28 2002 - 04:46:52 CDT