September 25, 2006 at 7:02 pm
Is there a way to create a view using SELECT * from 2 tables (actually in my case it's from 2 other views, but that shouldn't matter). I want to do the following:
CREATE VIEW COMBINE_VIEWS AS
SELECT * FROM VIEW1
INNER JOIN VIEW2 ON VIEW1.ID_FIELD = VIEW2.ID_FIELD
The problem is that the field that links the views (ID_FIELD) is in both views so I get this error "Column names in each view or function must be unique."
I know that if I specify each column name then I can just select the ID field from one of the views rather than both (or rename the field), but each view consists of many fields so I don't want to do it this way. Also, I will be adding fields to both views over time and want the combined view to include the new fields automatically. BTW, I have it split into 2 views intially because both views have one table in common (hence the common ID field) but the rest of the data is from different tables. It also works well for my users to have the 2 views separate and also to have them combined into 1 view.
The only other solution I could come up with is to change the name of the ID field in one of the original views so I won't have duplicate names, but I don't like this solution either (I know, I'm picky!). I don't want to do it this way since my users use the ID fields to link to other data (in ArcMap and other applications) and it would cause problems to have the ID field named differently in one of the views.
Is there some simple, elegant solution to this problem that I have overlooked? I realize that I may be trying to do the impossible. Any comments or suggestions would be appreciated.
THANKS!
September 25, 2006 at 7:25 pm
When you create a view, an entry is made in the syscolumns table for each column in the view. This is true even if you write the view with SELECT * FROM.
When you attempt to query columns from the view, the syscolumns table is used at parse time to determine if your query is valid. If you add a column to a base table after you created a view using SELECT * FROM, it will not be included in the view's syscolumns definition, and will not be part of the view.
-- Create a table
CREATE
TABLE dbo.SampleTable(
Col1
int NOT NULL,Col2
int NOT NULL)
GO
-- Create a view on the table
CREATE
VIEW dbo.SampleViewAS
SELECT * FROM dbo.SampleTableGO
-- Table's and view's entries in syscolumns. Note two columns for each.
SELECT
OBJECT_NAME(id), * FROM syscolumnsWHERE id IN (OBJECT_ID('dbo.SampleView'), OBJECT_ID('dbo.SampleTable'))
ORDER BY OBJECT_NAME(id)GO
-- Add a column to the table
ALTER
TABLE dbo.SampleTable ADD Col3 int NOT NULLGO
-- Table's entries in syscolumns: now has 3 columns.
-- View's entries in syscolumns: still has 2 columns.
SELECT
OBJECT_NAME(id), * FROM syscolumnsWHERE id IN (OBJECT_ID('dbo.SampleView'), OBJECT_ID('dbo.SampleTable'))
ORDER BY OBJECT_NAME(id)GO
-- New column not part of view, even though it looks like it should be.
-- Select * FROM view will only return 2 columns:
SELECT
* FROM dbo.SampleTableSELECT
* FROM dbo.SampleViewGO
-- Because new column not part of view, any specfic query for the new table columns
-- not in the query will throw an error:
SELECT
Col3 FROM dbo.SampleTableGO
SELECT
Col3 FROM dbo.SampleViewGO-- Clean up.
DROP
VIEW dbo.SampleViewDROP
TABLE SampleTableGO
-Eddie
Eddie Wuerch
MCM: SQL
September 26, 2006 at 2:32 am
hi
just give a alias-name(s) for the column(ID_FIELD) in the select statement of the view.
by the way why do you need to select ID_FIELD twice since u r joining on the ID_FIELD.
"Keep Trying"
September 26, 2006 at 3:52 pm
Thank you, Eddie. I didn't realize that SELECT * is not dynamic, and that is very good to know. It seems like it would be fairly easy to run an ALTER VIEW statement so that the added fields would show up in the view (I just have to remember to do it). That seems easier to me than having to manually write in all of the names of the added fields.
I guess what I really want to know is if there is a way to use SELECT * in a view that joins two tables, when the field that links the tables has the same name in both tables. Since the only duplicate field is the join field, and I really only want to have that field show up once in the result set, is there some way to do that?
I know some will say that using SELECT * is bad style and I should write out all of the field names anyway, but I work in a research environment where things are always changing. I am just trying to reduce the amount of work needed in response to changes in the way my users want to view the data.
September 26, 2006 at 8:23 pm
> Since the only duplicate field is the join field, and I really only want to have that field show up once in the result set, is there some way to do that?
SELECT * grabs all columns from all tables (and views and table functions) in the query. There isn't a way to block duplicate columns.
-Eddie
Eddie Wuerch
MCM: SQL
September 27, 2006 at 1:31 am
Hi,
SELECT * really is bad style, but there are places where it can help. I'm using it in one place only - we have some views that are basically reports, and I need to mail them automatically every day. The procedure that sends the mail finds addresses, does SELECT * FROM view ORDER BY column and mails the result. This way, when I'm asked to change a report, I can only change the view and don't have to do anything with the procedure. The view itself does NOT use *, all columns are named explicitly.
Using * has some limitations. It means ALL columns, and you can not exclude any. It also means you can't assign column names (aliases). Both these conditions together => all columns in the source tables/views must have unique names.
I wouldn't use SELECT * inside a view. It is better to name all columns you want to display and assign appropriate aliases, if necessary. I can understand why you wanted to do that, but IMHO it isn't a good solution - and in your case, when joining two tables (or views) that both have column with the same name, you simply can't use *, no matter how much that would help you...
What about the underlying views (view1, view2)? Do you use * there as well? If not, you could change the ID_FIELD name in one of these views with an alias - that would allow using * in your main view, but the same column would appear twice in it, with two different names. As I said, I don't recommend it... and as I just realized when reading your original post again, you don't want to do it (which is correct :-))
September 27, 2006 at 6:43 am
IMHO, views of views are a bit like writing functions that call functions... they sure seem useful until you need to troubleshoot...
--Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply