RE: RE: Create Table..As Select: Number formats

:31
>
>>>I'm trying to create a table using 'Create
>>>Table...As Select...'
>...
>
>> I don't think that there is any problem here.
>Specifying the number of
>digits is largely cosmetic - consider it as > a
>default mask. It doesn't
>affect how data is stored inside the tables AFAIK.
>>
>> Regards,
>>
>> Stephane Faroult
>> Oriole
>
>The problem is when I try to exchange the newly
>created table into the
>partitioned table - the designers (in their
>infinite wisdom) have
>specified number formats for that table.
>
>"alter table daily_total exchange partition jun_02
>with table dt_temp
>including indexes"
>
>gives me the error:
>
>*
>ERROR at line 1:
>ORA-14097: column type or size mismatch in ALTER
>TABLE EXCHANGE PARTITION
>
>Unless I get the column types to match exectly - I
>can't ask for a change
>in table design to remove the awkward formatting
>until I've at least tried
>to get the format to work in the 'Create Table...As
>Select..'
>
>Cheers
>Simon Anderson
>

Simon,

  Are you sure that it comes from the NUMBER() columns? Reminds me of the problem when you have a NULL in a UNION, which must be explicitly cast with a to_number(), to_date() or to_char(). Might it come from some NULL in your CREATE TABLE AS SELECT ... ?

Regards,

Stephane Faroult
Oriole

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).