Re: CTAS and ora-1723
- From: Tim Gorman <tim@xxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 01 Jul 2004 15:24:49 -0600
The NULL is the problem because Oracle doesn't know what datatype, scale, or
precision you want the PROVIDER column to be. And, as the error message
states, "zero-length columns" are not permitted...
How about breaking it up into two statements: a CREATE TABLE followed by an
INSERT ... SELECT?
on 7/1/04 1:04 PM, David at thump@xxxxxxxxxxxxxxxx wrote:
> Any ideas on why this would error out? Is it the nulls?
>
> create table tsteil.sub_duration_duration as (
> select NULL provider, a.user_id, a.subscription_id, a.game_code,
> a.timestamp, to_number(decode(a.duration, '14D', 14, '02D', 2, '40D', 40,
> '05D', 5, '10D', 10, '21D', 21, '30D', 30, '60D', 60, '90D', 90, '01M',
> 30, '03M', 60, '06M', 90, '12M', 360, '24M', 720, '07D', 7, '15D', 15,
> '01D', 1, '00D', 0, 'ERROR')) duration from tsteil.sub_duration_orders a
> UNION ALL select NULL provider, b.user_id, b.subscription_id, b.game_code,
> b.timestamp, to_number(decode(b.duration, '14D', 14, '02D', 2, '40D', 40,
> '05D', 5, '10D', 10, '21D', 21, '30D', 30, '60D', 60, '90D', 90, '01M',
> 30, '03M', 60, '06M', 90, '12M', 360, '24M', 720, '07D', 7, '15D', 15,
> '01D', 1, '00D', 0, 'ERROR')) duration from
> tsteil.sub_duration_account_keys b UNION ALL select NULL provider,
> c.user_id, c.subscription_id, c.game_code, c.timestamp,
> to_number(decode(c.duration, '14D', 14, '02D', 2, '40D', 40, '05D', 5,
> '10D', 10, '21D', 21, '30D', 30, '60D', 60, '90D', 90, '01M', 30, '03M',
> 60, '06M', 90, '12M', 360, '24M', 720, '07D', 7, '15D', 15, '01D', 1,
> '00D', 0, 'ERROR')) duration from tsteil.sub_duration_game_cards c UNION
> ALL select NULL provider, d.user_id, d.subscription_id, d.game_code,
> d.timestamp, to_number(decode(d.duration, '14D', 14, '02D', 2, '40D', 40,
> '05D', 5, '10D', 10, '21D', 21, '30D', 30, '60D', 60, '90D', 90, '01M',
> 30, '03M', 60, '06M', 90, '12M', 360, '24M', 720, '07D', 7, '15D', 15,
> '01D', 1, '00D', 0, 'ERROR')) duration from
> tsteil.sub_duration_rental_codes d
> )
> ORA-01723: zero-length columns are not allowed
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
