As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/

Need Database Query help

BuddiesBuddies Registered User regular
edited December 2009 in Help / Advice Forum
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 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?

Buddies on

Posts

  • DeebaserDeebaser on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
    edited December 2009
    SELECT
    columns
    FROM Table_1
    LEFT JOIN Table_2
    ON Table_1.IMG_SYSID =Table_2.IMG_SYSID
    WHERE
    Table_1.DATE >= '11/15/09'

    Deebaser on
  • BuddiesBuddies Registered User regular
    edited December 2009
    That was fast.

    Can I do

    CREATE TABLE new_table_name AS SELECT * FROM Table_1
    LEFT JOIN Table_2
    ON Table_1.IMG_SYSID =Table_2.IMG_SYSID
    WHERE
    Table_1.DATE >= '11/15/09'

    and I will now have a new table that includes the information I want? Sorry I have to ask, but I understand that the SELECT there will just give me a view that won't be a real table. I need it to be an actual table so I can export the table to another database.

    Buddies on
  • 1ddqd1ddqd Registered User regular
    edited December 2009
    I dont have much experience, but couldn't you export your results from each query and create tables in Access to make a new one? Just so you can use a graphical interface? Don't get me wrong, there's a way to do it using only SQL, but you'll have to go to your programs help file to figure out if it supports that kind of creation.

    1ddqd on
  • DeebaserDeebaser on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
    edited December 2009
    If you're using '*' as a substitute for the columns you are actually selecting, yes. If you're just pulling * from table one and left joining on table two without pulling any columns then you're going to just get all of table 1.

    Deebaser on
  • exmelloexmello Registered User regular
    edited December 2009
    I come from a sql server background so I'm working on the assumption that Oracle
    CREATE TABLE <tablename> AS SELECT ...
    
    behaves the same way as Sql Server
    SELECT <colA, colB...> INTO <tablename> FROM ...
    
    by just exporting the results of the select statement into a new table without keys or indexes or anything.

    I really would have to know exactly what you're trying to do before I know if what you're doing is the right solution though. If you need your results to be dynamic and not be a copy of the current state of table A and B, you should probably be using a VIEW.

    I rarely use SELECT INTO (in your case CREATE TABLE AS SELECT) unless I'm exporting data for a one-time custom report some executive handed down a request for. Otherwise, I would create the table definition beforehand with primary and foreign keys, constraints, and indexes. Then you can insert into the table using
    INSERT INTO tablename (colA, colB, ...) SELECT (colA, colB, ...) FROM ....
    

    exmello on
  • DeebaserDeebaser on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
    edited December 2009
    OT SQL complaint... Whoever the hell decided that some of these alphanumeric identifiers should be structured 11111111E11 should go to fucking hell. I've got better things to do than update scientific notation back into a character string.

    Deebaser on
  • BuddiesBuddies Registered User regular
    edited December 2009
    exmello wrote: »
    I really would have to know exactly what you're trying to do before I know if what you're doing is the right solution though. If you need your results to be dynamic and not be a copy of the current state of table A and B, you should probably be using a VIEW.

    Yea, I want the new table to be a copy of Table_1 and Table_2. I am going to take this new table, and export/copy it to an Access database.

    Buddies on
  • BuddiesBuddies Registered User regular
    edited December 2009
    Buddies wrote: »
    CREATE TABLE new_table_name AS SELECT * FROM Table_1
    LEFT JOIN Table_2
    ON Table_1.IMG_SYSID =Table_2.IMG_SYSID 
    WHERE
    Table_1.DATE >= '11/15/09';
    

    Gives me
    CREATE TABLE new_table_name AS SELECT * FROM Table_1
    *
    ERROR at line 1:
    ORA-00957: duplicate column name

    CREATE TABLE new_table_name AS SELECT * FROM Table_1
    LEFT JOIN Table_2.IMAGE
    ON Table_1.IMG_SYSID =Table_2.IMG_SYSID 
    WHERE
    Table_1.DATE >= '11/15/09';
    

    Cause I really only want that column, gives me
    LEFT JOING Table_2.Image
    *
    ERROR at line 2:
    ORA-00905: missing keyword


    :?

    Buddies on
  • JdNoaJdNoa Registered User regular
    edited December 2009
    CREATE TABLE new_table_name AS SELECT Table_1.*, Table_2.IMAGE
    FROM Table_1 LEFT JOIN Table_2 ON...
    

    unless there's a column in Table_1 called IMAGE.

    in which case, you'll need to rename the IMAGE column:
    CREATE TABLE new_table_name AS SELECT Table_1.*, Table_2.IMAGE AS IMAGE2
    FROM Table_1 LEFT JOIN Table_2 ON...
    

    JdNoa on
  • BuddiesBuddies Registered User regular
    edited December 2009
    the Column IMAGE from Table.2 is a LONG RAW BINARY type.

    Can I not do this because I am getting the error

    "ORA-00997: illegal use of LONG datatype"

    Buddies on
  • JdNoaJdNoa Registered User regular
    edited December 2009
    Buddies wrote: »
    the Column IMAGE from Table.2 is a LONG RAW BINARY type.

    Can I not do this because I am getting the error

    "ORA-00997: illegal use of LONG datatype"

    Googling this suggests that LONG RAW is tricky to work with. (I mostly use SQL Server myself, so not familiar with Oracle specific stuff).

    Would this be possible: on the computer with Access installed, create an ODBC data source to your Oracle database, use the Link Tables option in Access to view those Oracle tables, then do the import to the new table directly in Access?

    Otherwise, the workaround I see suggested for copying LONG RAW fields is to export the table (Table_2), rename it (to new_table_name), import Table2 back again, then modify new_table_name as needed. (In your case, dropping the unneeded columns, adding the Table_1 columns, then running an update query to copy the data across.)

    JdNoa on
  • vonPoonBurGervonPoonBurGer Registered User regular
    edited December 2009
    Yes, LONG RAW is tricky to work with. You could maybe do what you want to do with export/import, but it would be a monumental pain in the ass. You'd need to export Table2 using the query option to restrict your export to rows with a date of 11/15/09 or later. You'd need to rename the existing Table2 to something else, then reimport your export file. Then you'd need to shuffle the table names for the old Table2 and the newly imported Table2. Then you'd need to drop all the columns you didn't want from the new Table2, add all the columns from Table1 to the new Table2, then populate those columns with a massive update statement using correlated subqueries.

    As you can see, that's... pretty labour intensive. Why do you need this new table anyway? Is it really necessary to duplicate these specific images from Table2 and get the associated data from Table1 into a single table? Storing the same data in two or more places in a single database is usually considered a no-no.

    vonPoonBurGer on
    Xbox Live:vonPoon | PSN: vonPoon | Steam: vonPoonBurGer
  • JdNoaJdNoa Registered User regular
    edited December 2009
    As you can see, that's... pretty labour intensive. Why do you need this new table anyway? Is it really necessary to duplicate these specific images from Table2 and get the associated data from Table1 into a single table? Storing the same data in two or more places in a single database is usually considered a no-no.

    S/he wants to import this particular set of data into Access.

    Buddies, if you don't want to go the ODBC route, can you just export both tables, import them both into Access, and do your data manipulation there? If that would be too slow, I really would recommend trying ODBC... I haven't ever connected to Oracle that way but I've used this method to import data into Access from SQL Server, MySQL, and Paradox.

    JdNoa on
  • JHunzJHunz Registered User regular
    edited December 2009
    See the following workaround:
    http://www.dbaxchange.com/3_4_longdatatypes.htm

    So try the following:

    copy from <user>/<pass>@&lt;SID> CREATE table_3 USING select table_1.*,table_2.IMAGE from table_1,table_2 where table_1.img_sysid=table_2.img_sysid and table_1.datecol > to_date('11/15/2009','MM/DD/YYYY');

    Just tried it with a 10G database set up like your tables and it seemed to work fine.

    JHunz on
    bunny.gif Gamertag: JHunz. R.I.P. Mygamercard.net bunny.gif
  • BuddiesBuddies Registered User regular
    edited December 2009
    JdNoa wrote: »
    As you can see, that's... pretty labour intensive. Why do you need this new table anyway? Is it really necessary to duplicate these specific images from Table2 and get the associated data from Table1 into a single table? Storing the same data in two or more places in a single database is usually considered a no-no.

    S/he wants to import this particular set of data into Access.

    Buddies, if you don't want to go the ODBC route, can you just export both tables, import them both into Access, and do your data manipulation there? If that would be too slow, I really would recommend trying ODBC... I haven't ever connected to Oracle that way but I've used this method to import data into Access from SQL Server, MySQL, and Paradox.

    Thanks man, I did the ODBC thing and linked through Access and was able to get what I wanted.

    Thanks for all your suggestions everyone else.

    Buddies on
Sign In or Register to comment.