CREATE VIEW with "SELECT * FROM" several tables [message #667170] |
Tue, 12 December 2017 09:24 |
Rumak18
Messages: 20 Registered: April 2009 Location: Germany
|
Junior Member |
|
|
Hi folks,
there is a provider who states that he needs to get several tables from a schema in our database.
I've tried the following which obviously crashed:
CREATE VIEW 5600_VIEW AS SELECT * FROM TABLE1,TABLE2,TABLE3,TABLE4,TABLE5,TABLE6,TABLE7,TABLE8;
Unfortunately he needs to view over 80 tables.
I got the error "ora-00957 duplicate column name".
How can i realize issues like that?
|
|
|
|
|
Re: CREATE VIEW with "SELECT * FROM" several tables [message #667173 is a reply to message #667172] |
Tue, 12 December 2017 10:41 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You should be very glad there's duplicate column names otherwise that query may well have maxed out the servers CPU.
John mentioned cartesian product - that's where every row in 1 table is joined to every row in another table. You've got 8 tables cartesianed together. So every row in table 1 will be joined to every row in table2 and every row in table3 and ....... table8.
So the total number of rows it returns is: number of rows in table1 * number of rows in table2 * number of rows in table3 * ..... table8
Unless those tables are tiny that number is going to be gargantuan.
If the tables data is related then you need to write a select/view that specifies the relationships in join or where clauses.
If they aren't related they should be queries seperately and BlackSwan's suggestion is probably what you need.
|
|
|
|
|
|
|
|
|
|
|
Re: CREATE VIEW with "SELECT * FROM" several tables [message #667231 is a reply to message #667229] |
Fri, 15 December 2017 07:40 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
Rumak18 wrote on Fri, 15 December 2017 05:32Hi,
i do understand this topic , but there is just no other user that can distribute privileges like "GRANT SELECT ON ..." to someone.
You should use SYS user to make NEW application user who has necessary privileges to manage the application & not use SYS or SYSTEM schemas
|
|
|