-
Hi all,
I am trying to create a table from other existing table as follows:
create table xxx as select distinct * from yyy;
I think you can see my point here is trying to eliminate the duplicated rows in table yyy. However, I'm getting "inconsistent datatype" when this statement is run.
Can someone advise me how it can be achieved. Thanx.
-
What version of Oracle and SQL/Plus are you using?
I just tried this on a test table on Oracle 8i and it worked for me ?
- Rajeev
Rajeev Suri
-
Hi
May be you use LONG type in the table, in this case it will not be work.
Best wishes
Dmitri
-
actually I too thought about the LONG datatype but in that case you will get a different error --
"ORA-00997: illegal use of LONG datatype"
Coud you post the result of DESCRIBE of the source table ?
Also, is this the exact syntax you are using -- "create table xxx as select distinct * from yyy;" ?
- Rajeev
Rajeev Suri
-
Yes, there is a column with CLOB as its datatype, could that be the reason?
When I create table without the DISTINCT key word it worked but not when having it.
-
New To Oracle .. Pls Help ME!
I have been developing dbms application for 10+ yrs.
I have been developing a PRO*C, C, w/ 7.3.4 application for the past 6 months... I have been a db2 appliations dba...
Would like to become an oracle applications dba...
looking for advice boooks web sights...
any help would be greatly appreciated.
TIA, Monty
Multi-Platform Database Developer
Member of javaranch.com
-
Yes, CLOB is the problem.
So the problem is not creating the table but actually selecting the CLOB with DISTINCT. Try the following query; I am sure it will fail and give you the same error message --
select distinct * from yyy;
I tried this on a test table --
SQL> select distinct * from test2;
select distinct * from test2
*
ERROR at line 1:
ORA-00932: inconsistent datatypes
- Rajeev
Rajeev Suri
-
So what should I do if I'd like to have distinct rows?
-
what is the minimum number of columns you need to be unique on? have you tried listing them explicitly?
create table new as select distinct col1, col2, col3,...
i know this won't work with a long but i don't know about the clob influence. maybe CTAS will work if the CLOS is explicitly queried. LOBs were created to get around these sorts of troubles but i haven't worked with them. report back if it works, will you?
d.
-
As per LOB's restrictions, U can't use select distinct or order by or group by clause.
Select distinct are allowed on LOB attributes if the object type has a MAP or ORDER function associated with it.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|