DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Create table problem

  1. #1
    Join Date
    Dec 2000
    Posts
    87
    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.

  2. #2
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    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

  3. #3
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688
    Hi
    May be you use LONG type in the table, in this case it will not be work.

    Best wishes
    Dmitri

  4. #4
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    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

  5. #5
    Join Date
    Dec 2000
    Posts
    87
    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.

  6. #6
    Join Date
    Apr 2001
    Location
    Boston Ma
    Posts
    3

    Lightbulb 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

  7. #7
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    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

  8. #8
    Join Date
    Dec 2000
    Posts
    87
    So what should I do if I'd like to have distinct rows?

  9. #9
    Join Date
    Oct 2000
    Location
    Cambridge, MA (Boston)
    Posts
    144
    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.

  10. #10
    Join Date
    Nov 2000
    Posts
    224
    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
  •  


Click Here to Expand Forum to Full Width