Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2003
    Posts
    17

    Unanswered: Create table as select

    Hi folks,

    I'd like to create a new table with stuff from another table but i'd like to add a new custom field.

    Is it possible to add the new field while creating the table like this :

    create table mytable (
    newfield integer,
    (select field1, field2, field3 from theothertable))

    (this won't work)

    Or I have to create the table like this :
    create table mytable as select field1, field2, field3 from the othertable

    and then alter the table to add the new field.

    Your suggestions are welcome,

    Thanks in advance,

    Best regards,

    Zteev

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Well, you can do this:

    create table mytable as select field1, field2, field3, 0 newfield
    from the othertable;

    That will create the column "newfield" with datatype NUMBER (not INTEGER) and defaulted to 0 for all rows. However, you will not be able to alter the datatype of newfield without setting it to NULL first:

    update mytable set newfield=null;
    alter table mytable modify newfield integer;

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Just include the field in your select statement as well (create table mytable as select field1, field2, field3, 0 integer_field from yourtable). You will have to default it to some value depending on the kind of data you want to insert.

  4. #4
    Join Date
    Oct 2004
    Posts
    3
    Hi, I have a question and Ithink it is realted to this problem

    I have a table that is contained a fullName as one of its attribute.

    I would like to create another table that divide the fullName from the the first table into a firstName , middleName(if exist) and LastName.

    could any one give a hint about this.

    Thank you

  5. #5
    Join Date
    Jan 2004
    Posts
    1

    hi

    hi all

    did u try the simple way of select into ?

    i.e.
    select A.yourfields into table B..

    it creates a new table B with the returned rows of ur select statement

    bb


    just a mirage.

  6. #6
    Join Date
    Apr 2004
    Posts
    246
    miraggio -
    that's not even valid sql

    Zteev -
    why do feel the need to return your data as cursors? do mean ref cursors? why not just return the data as arrays? And, if the first cursor returns 10 rows, would the 2nd cursor return 10 sets of data - assuming that each query on it would return 3 rows, then cursor 2 would return 30 rows - so how do plan to connect the data between the two cursors?
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    shoblock, did you respond to the wrong thread? No one mentioned cursors until you just did

  8. #8
    Join Date
    Apr 2004
    Posts
    246
    whoops - better wake up before I type these things
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  9. #9
    Join Date
    Oct 2004
    Posts
    3
    I really didn't get what you mean!!
    Can you just explain more?

  10. #10
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    sakab, basically, if the FullName attribute is compound of Firstname <space> Lastname, you can using substr() and instr() functions, to get part of the string and to locate the char in which you want to substr from.

    in example:
    Code:
    SQL@8i> create table t as select 'Joaquin Martinez' fullname from dual;
    
    Table created.
    
    SQL@8i> select * from t;
    
    FULLNAME
    ----------------
    Joaquin Martinez
    
    SQL@8i> create table t_copy as select substr(fullname,1,instr(fullname,' ')) firstname,
      2                                   substr(fullname,instr(fullname,' ')+1) lastname
      3                              from t
      4  /
    
    Table created.
    
    SQL@8i> select * from t_copy;
    
    FIRSTNAME        LASTNAME
    ---------------- ----------------
    Joaquin          Martinez
    
    SQL@8i>

  11. #11
    Join Date
    Oct 2004
    Posts
    3
    Thank you,

    But how if you have more than 3 names?

    Ex, Joaquin MiddleName1 MiddleName2 Martinez

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •