Default Column Value in Create Table As Select

[bflochick]

I hope this is the right place for this. I did not see an Oracle Sql forum.
I'm having a mental block today.

I am creating a table as select from a few other tables. My problem is, a couple of the columns that I would like included in this table cannot be selected from another table and I would like them to be a default value at all times.

Example:

CREATE TABLE test
AS
SELECT a.col1,
a.col2,
'999', <---**default value***
b.col1,
b.col2
FROM table1 a, table2 b...

I tried putting the line in like this and I error out:

colname varchar2(3) default '999',

Is this even possible?

 

[karluk]

Your only syntax error is that you didn't provide a name for the column. The following minor change will work

CREATE TABLE test
AS
SELECT a.col1,
   a.col2,
   '999' colname      <---**default value***
   b.col1,
   b.col2
FROM table1 a, table2 b...

 

[bflochick]

Thank You!

 

[SantaMufasa]

Chick,

I imagine that Karl's suggestion was valuable...I'll bet you wanted to click the 'Thank karluk for this valuable post' link, right?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]