January 12, 2010 at 10:12 am
I'm aware that SELECT * is not best practice and that it's best to alias tables and then use the table alias when listing out each column within a View that you need to see.
My problem is I have been given a large select that needs to be saved as a View for reporting. Within the select it joins on 3 tables that contain user defined (dynamic) columns that can be different for each client. So it's necessary to use a SELECT * because we have no way of knowing what the columns are the users have defined. The issue is that within each of these tables there is a column UpdateUserID that is not user defined and because of the SELECT * in the view you cannot create the view because it see's this column as not being Unique.
The only way I can think to deal with this is by creating a stored procedure that builds the columns in the select by using dynamic SQL.
Is there a way to accomplish what I need within a View?
I didn't think there was a way to exclude a column when using SELECT * but I thought I'd ask.
Thanks
January 12, 2010 at 10:19 am
Can you provide a simplified version of what you are attempting to accomplish including table definitions (CREATE TABLE stements), smaple data (INSERT INTO statements for each table), and expected results based on the sample data?
I think this would go a long way to helping you.
January 12, 2010 at 10:25 am
Can't think of a way, no.
I'll come back to this is the morning, but my initial reaction is that it can't be done.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 12, 2010 at 12:41 pm
Okay here is a pretty simple example. Keep in mind each client could have different columns within these tables as some of them can be user defined. There are also columns that are not user defined that exist in both (DealHeaderID, UpdateUserID).
CREATE TABLE [dbo].[Deal1](
[DealID] [int] IDENTITY(1,1) NOT NULL,
[DealHeaderID] [int] NOT NULL,
[UpdateUserID] [int] NULL,
[215] [char] (1) NULL,
[date2] [datetime] NULL
)
CREATE TABLE [dbo].[DealHeader1](
[DealHeaderID] [int] IDENTITY(1,1) NOT NULL,
[DealID] [int] NOT NULL,
[UpdateUserID] [int] NULL,
[300] [char] (1) NULL,
[date500] [datetime] NULL,
[7878] [datetime] NULL,
[5476] [datetime] NULL,
[54645] [char](1) NULL
)
CREATE VIEW dbo.vwDeal
AS
SELECT
D.*,
DH.*
FROM Deal1 D
INNER JOIN DealHeader1 DH ON DH.DealID = D.DealID
If you create the first two tables and then try to create the view you will get an error on DealHeaderID for not being unique.
Msg 4506, Level 16, State 1, Procedure vwDeal, Line 3
Column names in each view or function must be unique. Column name 'DealHeaderID' in view or function 'vwDeal' is specified more than once.
January 12, 2010 at 1:55 pm
BH-428850 (1/12/2010)
Okay here is a pretty simple example. Keep in mind each client could have different columns within these tables as some of them can be user defined. There are also columns that are not user defined that exist in both (DealHeaderID, UpdateUserID).CREATE TABLE [dbo].[Deal1](
[DealID] [int] IDENTITY(1,1) NOT NULL,
[DealHeaderID] [int] NOT NULL,
[UpdateUserID] [int] NULL,
[215] [char] (1) NULL,
[date2] [datetime] NULL
)
CREATE TABLE [dbo].[DealHeader1](
[DealHeaderID] [int] IDENTITY(1,1) NOT NULL,
[DealID] [int] NOT NULL,
[UpdateUserID] [int] NULL,
[300] [char] (1) NULL,
[date500] [datetime] NULL,
[7878] [datetime] NULL,
[5476] [datetime] NULL,
[54645] [char](1) NULL
)
CREATE VIEW dbo.vwDeal
AS
SELECT
D.*,
DH.*
FROM Deal1 D
INNER JOIN DealHeader1 DH ON DH.DealID = D.DealID
If you create the first two tables and then try to create the view you will get an error on DealHeaderID for not being unique.
Msg 4506, Level 16, State 1, Procedure vwDeal, Line 3
Column names in each view or function must be unique. Column name 'DealHeaderID' in view or function 'vwDeal' is specified more than once.
Now, we need some sample data for the tables (INSERT INTO statements) and expected results based on the sample data (what should the result of query used to create the view in this case if everything worked correctly).
January 12, 2010 at 2:10 pm
I don’t see the relevance of the data in this situation. My goal is to create a view in which I can select all user defined columns from both tables without prompting an error on the columns that are not user defined and are not unique. If you have an answer as to how I can do this then I can apply your advice into my real scenario and verify the data.
January 12, 2010 at 2:12 pm
How do I know if I have the correct answer if you don't show me what the correct answer is? How do I test with out test data?
January 12, 2010 at 2:28 pm
The only way I can think of to do this would be dynamic SQL.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2010 at 2:29 pm
Well the fact of the problem is that it is a schema issue and not a data issue (having to do with columns returned in a view). If you created the two example tables I provided and then try to create the view I provided it will produce an error which in this case is your test scenario.
If you find a way to create the view while selecting back all of the user defined columns and a unique version of the columns that exist in both that are not user defined columns then you will have provided me with the correct answer to the test scenario.
Even if I provided data it would be made up irrelevant data as the tables I’ve provided were made up in order to provide you a simplified version of the example.
Any help is appreciated but I don’t see the point in generating bogus data that has nothing to do with a schema issue.
January 12, 2010 at 2:46 pm
The only way to do what you want is to explicitly list all the column names from both tables and then alias one or both of the column names that are duplicated in both tables, like this:
CREATE VIEW dbo.vwDeal
AS
SELECT
D.DealID as D_DealerID,
D.DealHeaderID as D_DealHeaderID,
D.UpdateUserID as D_UpdateUserID,
D.[215],
D.date2,
DH.DealHeaderID as DH_DealHeaderID,
DH.DealID as DH_DealID,
DH.UpdateUserID as DH_UpdateUserID,
DH.[300],
DH.date500,
DH.[7878],
DH.[5476],
DH.[54645]
FROM
dbo.Deal1 D
INNER JOIN dbo.DealHeader1 DH
ON DH.DealID = D.DealID;
January 12, 2010 at 3:17 pm
How can I explicitly list all of the column names from both tables when I won’t have any knowledge of what the user defined columns are when they are different on each client’s database? That’s why I said SELECT * would be necessary in my initial post.
January 12, 2010 at 3:23 pm
The problem is duplicate column names between the tables. If the columns names in all the tables were guarantteed to be unique, you could get away with the * in the select. Anytime the changes are made to the underlying tables, you would still need to refresh the view as it would not automatically catch those changes.
Your other option is to use dynamic sql to generate the view. Not knowing how it is being used, not sure how this would work. Not enough information to provide you with alternatives.
January 12, 2010 at 3:33 pm
Yes it seems you would need some fairly hairy dynamic SQL to produce a CREATE VIEW statement. You would need to decide on some scheme to rename columns whose names collide. It is hard to think how this might work neatly in practice - presumably there are other things that would need to be coded to work with the view...?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 12, 2010 at 3:43 pm
Yes, I knew what the problem is and that is has to do with duplicate columns not being unique. I figured I could use dynamic sql in order to complete the select I want with a stored procedure but since I’m using this for reporting purposes I need it work within a view. Using dynamic sql to create the view might be a challenge as I would need to continually run some sort of a job to rebuild the view based on when a client adds or changes a user defined column in one of the tables.
It looks like I’ll have to chalk this one up as not being possible within a view.
January 12, 2010 at 4:24 pm
BH-428850 (1/12/2010)
It looks like I’ll have to chalk this one up as not being possible within a view.
...and not possible with a T-SQL table-valued function either, since dynamic SQL is not allowed. In principle, a CLR TVF could be made to work, but performance would be awful since the entire view would probably have to be materialized.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply