Need Database Query help

Buddies

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?

Deebaser

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'

Buddies

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.

1ddqd

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.

Deebaser

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.

exmello

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 ....

Deebaser

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.

Buddies

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

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


:?

JdNoa

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...

Buddies

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"

JdNoa

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.)

vonPoonBurGer

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.

JdNoa

vonPoonBurGer 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.

JHunz

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.

Buddies

JdNoa wrote: »

vonPoonBurGer 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.