1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Creating a new table from two Existing ones

Discussion in 'SQL PL/SQL' started by myrkur, Dec 18, 2009.

  1. myrkur

    myrkur Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I have very little Database Experience, and I need a little help. I am using sqlplus to manipulate an Oracle 10g database.

    I have two tables I want to use to create a new table.

    Table_1 has a column that references data in Table_2. I want my new table to contain all of the information from Table_1 + one column of Table_2. Table_2 includes data from a bunch of other tables I do not want. Table_2 also has a DATE column that is relevant. So I only want the information from Table_2 where it matches Table_1 AND where the DATE is after 11/15/09.

    IMG_SYSID is the column in both tables. IMAGE is the column from table_2 that I want to add to my new table along with all the information from table_1.


    "CREATE TABLE new_table_name AS SELECT * FROM Table_1"

    Will give me a copy of Table_1, right?

    Now how do I add the information from Table_2.IMAGE where Table_2.IMG_SYSID matches Table_1.IMG_SYSID AND DATE > 11/15/09 (between 11/15/09 and today). Is it a JOIN WHERE clause of some sort? Do I need to do it all in one query?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You do need it all in one query and it does require a join. You did not provide the column list from Table_1; this may work, though:

    Code (SQL):
    CREATE TABLE my_new_table
    AS
    SELECT a.*, b.image
    FROM table_1 a JOIN table_2 b ON (b.img_sysid = a.img_sysid)
    WHERE b.DATE > to_date('11/15/09','MM/DD/YY');
    DATE is a reserved word in Oracle; I am surprised it's allowing that column name in queries. It is much better to include all columns by name in such queries instead of using the shortcut '*'.
     
  3. myrkur

    myrkur Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I am getting
    ERROR at line 3:
    ORA-00997: illegal use of LONG datatype

    Here are my tables.

    Code (SQL):
    SQL> DESC IMG.STAFF_AND_OTHERS
     Name                                               NULL?    TYPE
     -------------------------------------------------- -------- -------------
    ----------------
     BADGE_ID                                                    VARCHAR2(6)
     NAME                                                        VARCHAR2(30)
     SSN                                                         VARCHAR2(11)
     DOB                                                         DATE
     ISSUEDATE                                                   DATE
     RANK                                                        VARCHAR2(16)
     CIVTYPE                                                     VARCHAR2(20)
     SEX                                                         VARCHAR2(1)
     RACE                                                        NUMBER(1)
     HEIGHT                                                      NUMBER(3)
     WEIGHT                                                      NUMBER(3)
     EYES                                                        VARCHAR2(3)
     HAIR                                                        VARCHAR2(3)
     BUILD                                                       VARCHAR2(2)
     COMPLEXION                                                  VARCHAR2(10)
     TATOOS                                                      VARCHAR2(1)
     MARKS                                                       VARCHAR2(1)
     COMMENTS                                                    VARCHAR2(150)
     IMG_SYSID                                          NOT NULL NUMBER(38)
     EXPIREDDATE                                                 DATE
     ADDRESS                                                     VARCHAR2(30)
     STATE                                                       VARCHAR2(2)
     ZIPCODE                                                     NUMBER(5)
     CITY                                                        VARCHAR2(20)
    Code (SQL):
    SQL> DESC IMG.IMG_S_DATA_IMAGE
     Name                                               NULL?    TYPE
     -------------------------------------------------- -------- ------------
    ----------------
     DATABASE_NAME                                      NOT NULL CHAR(35)
     IMG_VIEW                                           NOT NULL CHAR(35)
     IMG_SYSID                                          NOT NULL NUMBER(38)
     DATE_TIME_TAKEN                                    NOT NULL DATE
     IMG_DESCRIPTION                                             CHAR(60)
     IMAGE                                                       LONG RAW
     IMG_NUMBER                                         NOT NULL NUMBER(38)
    Can I not do what I want because of the long raw binary data(for an image)?

    as explained, I want .STAFF_AND_OTHERS with IMAGE from IMG.IMG_S_DATA_IMAGE tacked onto it where IMG_SYSID match and from 11/15/09 to now.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You cannot use a "create table as select" in this situation nor can you use "insert into ... select" to maintain this data if you have a long raw column in this new table. Had you provided this information in your original post you could have had a working solution by now. What is the purpose of this new table of yours? How do you plan on maintaining the data in this new table? You must think this through very carefully as you will need to create your new table with a BLOB column for IMAGE:

    Code (SQL):
    SQL> CREATE TABLE IMG.STAFF_AND_IMAGE
    (BADGE_ID                                                    VARCHAR2(6).
     NAME                                                        VARCHAR2(30).
     SSN                                                         VARCHAR2(11).
     DOB                                                         DATE.
     ISSUEDATE                                                   DATE.
     RANK                                                        VARCHAR2(16).
     CIVTYPE                                                     VARCHAR2(20).
     SEX                                                         VARCHAR2(1).
     RACE                                                        NUMBER(1).
     HEIGHT                                                      NUMBER(3).
     WEIGHT                                                      NUMBER(3).
     EYES                                                        VARCHAR2(3).
     HAIR                                                        VARCHAR2(3).
     BUILD                                                       VARCHAR2(2).
     COMPLEXION                                             VARCHAR2(10),
     TATOOS                                                      VARCHAR2(1),
     MARKS                                                       VARCHAR2(1),
     COMMENTS                                                VARCHAR2(150),
     IMG_SYSID                                           NUMBER(38) NOT NULL,
     EXPIREDDATE                                                 DATE,
     ADDRESS                                                     VARCHAR2(30),
     STATE                                                       VARCHAR2(2),
     ZIPCODE                                                     NUMBER(5),
     CITY                                                        VARCHAR2(20),
     IMAGE                                                      BLOB);
     
    You will then need to use the TO_LOB() function to populate the table:

    Code (SQL):
    inser INTO iimg.staff_and_image
    (BADGE_ID, NAME, SSN, DOB, ISSUEDATE, RANK, CIVTYPE, SEX, RACE, HEIGHT,
     WEIGHT, EYES, HAIR, BUILD, COMPLEXION, TATOOS, MARKS, COMMENTS,
     IMG_SYSID, EXPIREDDATE, ADDRESS, STATE, ZIPCODE, CITY, IMAGE)
    SELECT
    ( BADGE_ID, NAME, SSN, DOB, ISSUEDATE, RANK, CIVTYPE, SEX, RACE, HEIGHT,
     WEIGHT, EYES, HAIR, BUILD, COMPLEXION, TATOOS, MARKS, COMMENTS,
     IMG_SYSID, EXPIREDDATE, ADDRESS, STATE, ZIPCODE, CITY, to_lob(IMAGE))
    FROM img.staff_and_others s JOIN img.img_s_data_image i ON (i.img_sysid = s.img_sysid)
    WHERE i.date_time_taken > to_date('11/15/09','MM/DD/YY');
     
    Possibly this is a task for someone with more experience?