Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Newbie has Oracle questions…

Re: Newbie has Oracle questions…

From: <doug_clark_20_at_my-deja.com>
Date: Tue, 16 Jan 2001 15:43:29 GMT
Message-ID: <941q6v$pmc$1@nnrp1.deja.com>

In article <3A63DFDF.F93C64BD_at_ese.washington.edu>,   Ion Manea <imanea_at_ese.washington.edu> wrote:
> > Newbie has Oracle questions…
> >
> > 1. I’ve got a whole bunch of table in a Oracle database I want to
> > concatenate into a single view, where I would like to be able to
 modify
> > the data in the view if I wished. All the separate tables have a
 index
> > that is unique for all of the tables. So far I’ve tried the below…
> >
> > Create view NEW-VIEW as
> > Select * from X-A
> > Union select * from X-B
> > Union select * from X-C
> >
> > And so on…
> >
> > I’ve not been able to test whether this locks the records or not yet
> > but I expect it to from my MS-Access experience. I’ve seen this
 done
> > on SQL server in the past but can’t remember how?
> >
> > 2. I’ve tried to link the above created view into an MS-Access
 database
> > using the Oracle ODBC driver. I’ve successfully linked in other
 tables
> > from the same Oracle database but with this view I get the error
 ORA-
> > 01013 and a text message which I can’t remember now I’m at home.
> > What’s up with this then?
> >
> > We’re running Oracle 8.0.5. I would ask our Oracle administrator
 if we
> > had one, so I’m in fumble around mode at the moment.
>
> Oracle, unlike SQL Server and Access is not a toy. What you are
 trying to
> do will work if each row of each table is identical but you MUST have
 a
> WHERE clause that defines the relationship between the tables. And if
 you
> have multiple tables with absolutely identical fields then it begs the
> question why you don't just put them all into a single table which
 would be
> substantially faster.
>
> If the tables are not identical in structure, but substantially the
 same it
> is still bad design but you can make the UNIONs work by creating dummy
> fields like this:
>
> select field1, field2, field3
> from table1
> union
> select field1, null, field3
> from table2
>
> Hope this helps.
>
> Daniel A. Morgan
>
>

All the tables are identical in structure. It would make more sense if it were just one big table in the first place but this is how the application that generates the data is written and I don't think I'm going to be able to get it changed. So I'm stuck trying to nail all the tables together so I can do somthing usful with the data.

Sent via Deja.com
http://www.deja.com/ Received on Tue Jan 16 2001 - 09:43:29 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US