Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Newbie has Oracle questions…
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