FreeLists / oracle-l / RE: CTAS and ora-1723

["Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>]

I think Oracle is not able to derive a datatype for those columns where you
specify NULL...
and you are creating a table ...
Kind regards,
Lex.

---------------------------------------------
visit my website at http://www.naturaljoin.nl
---------------------------------------------



-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of David
Sent: Thursday, July 01, 2004 20:04
To: oracle-l@xxxxxxxxxxxxx
Subject: CTAS and ora-1723


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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


-- Binary/unsupported file stripped by Ecartis --
-- Type: text/x-vcard
-- File: Lex de Haan.vcf


----------------------------------------------------------------
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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------