|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Adding non-table field to a view?Is it possible to add a non-table field (i.e. a new field that does not
belong to any table) to a view? I am trying to avoid creating a brand new table with just one field so that I can add it to the view. Any help will be very much appreciated. Like
Select column1, column2, 'Hi there' From MyTable Assumes you have Column1 and column2 in MyTable -- Show quoteKevin Hill President 3NF Consulting www.3nf-inc.com/NewsGroups.htm www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs. www.experts-exchange.com - experts compete for points to answer your questions "simon" <si***@discussions.microsoft.com> wrote in message news:514257EA-DD25-44F4-B1DC-A69783FCE841@microsoft.com... > Is it possible to add a non-table field (i.e. a new field that does not > belong to any table) to a view? I am trying to avoid creating a brand new > table with just one field so that I can add it to the view. > > Any help will be very much appreciated. > KH and Kevin, thanks for the quick response.
Your scripts are great. However, the new field is going to be a flag that needs to be turned on and off periodically. Since a view is not updatable, what do you think is the best way to handle this? Of course, the best route is to add the flag to the original table. But since the table is from a vendor application, altering it requires moving mountains. I am looking for a workaround. Thank you so much for your help. Show quote "Kevin3NF" wrote: > Like > > Select column1, column2, 'Hi there' > From MyTable > > Assumes you have Column1 and column2 in MyTable > > > -- > Kevin Hill > President > 3NF Consulting > > www.3nf-inc.com/NewsGroups.htm > > www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs. > > www.experts-exchange.com - experts compete for points to answer your > questions > > > "simon" <si***@discussions.microsoft.com> wrote in message > news:514257EA-DD25-44F4-B1DC-A69783FCE841@microsoft.com... > > Is it possible to add a non-table field (i.e. a new field that does not > > belong to any table) to a view? I am trying to avoid creating a brand new > > table with just one field so that I can add it to the view. > > > > Any help will be very much appreciated. > > > > > > Since a view is not updatable, I think you are confusing "update" with "alter"... never mind that views *can* be updateable, whenever you want to change the value you can use ALTER VIEW. e.g. CREATE VIEW dbo.myView AS SELECT col1, col2, col3 = 'my value' FROM table GO SELECT * FROM dbo.myView GO -- now let's change the value ALTER VIEW dbo.myView AS SELECT col1, col2, col3 = 'a different value' FROM table GO SELECT * FROM dbo.myView GO -- you can even drop the column altogether -- but this will probably break scripts ALTER VIEW dbo.myView AS SELECT col1, col2 FROM table GO Notice the use of a column name on the constant value. I think you might run into problems if you don't bother naming all of the columns/expressions in the view's SELECT statement... Turned on and off periodically? You may have a calculated column in a view
whose value is derived from other table columns and functions. In the example below, the ExpiredFlag column is 0 or 1 depending on if the quote is currently expired. select QuoteID, QuoteAmt, case when ExpirationDate > getdate() then 1 else 0 end as ExpiredFlag from Quotes Show quote "simon" <si***@discussions.microsoft.com> wrote in message news:726A3187-0A9B-4E8A-AD2A-E2D594300DB9@microsoft.com... > KH and Kevin, thanks for the quick response. > > Your scripts are great. However, the new field is going to be a flag that > needs to be turned on and off periodically. Since a view is not updatable, > what do you think is the best way to handle this? > > Of course, the best route is to add the flag to the original table. But > since the table is from a vendor application, altering it requires moving > mountains. I am looking for a workaround. > > Thank you so much for your help. > > > > "Kevin3NF" wrote: > > > Like > > > > Select column1, column2, 'Hi there' > > From MyTable > > > > Assumes you have Column1 and column2 in MyTable > > > > > > -- > > Kevin Hill > > President > > 3NF Consulting > > > > www.3nf-inc.com/NewsGroups.htm > > > > www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs. > > > > www.experts-exchange.com - experts compete for points to answer your > > questions > > > > > > "simon" <si***@discussions.microsoft.com> wrote in message > > news:514257EA-DD25-44F4-B1DC-A69783FCE841@microsoft.com... > > > Is it possible to add a non-table field (i.e. a new field that does not > > > belong to any table) to a view? I am trying to avoid creating a brand new > > > table with just one field so that I can add it to the view. > > > > > > Any help will be very much appreciated. > > > > > > > > > In certain cases, a view can be updateable, but that would not help you
here. A view has no storage of its own, so adding a 'flag' column to a view will be of no use. Since you are unable to modify the underlying table, you will probably need to create a secondary table having a 1-to-1 relationship with the original table. This secondary table would contain the flag column (and the corresponding Primary Key columns from the original table, of course). You can then create a view incorporating the two tables if you wish. IHTH Jeremy Show quote "simon" <si***@discussions.microsoft.com> wrote in message news:726A3187-0A9B-4E8A-AD2A-E2D594300DB9@microsoft.com... > KH and Kevin, thanks for the quick response. > > Your scripts are great. However, the new field is going to be a flag that > needs to be turned on and off periodically. Since a view is not updatable, > what do you think is the best way to handle this? > > Of course, the best route is to add the flag to the original table. But > since the table is from a vendor application, altering it requires moving > mountains. I am looking for a workaround. > > Thank you so much for your help. > > > > "Kevin3NF" wrote: > > > Like > > > > Select column1, column2, 'Hi there' > > From MyTable > > > > Assumes you have Column1 and column2 in MyTable > > > > > > -- > > Kevin Hill > > President > > 3NF Consulting > > > > www.3nf-inc.com/NewsGroups.htm > > > > www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs. > > > > www.experts-exchange.com - experts compete for points to answer your > > questions > > > > > > "simon" <si***@discussions.microsoft.com> wrote in message > > news:514257EA-DD25-44F4-B1DC-A69783FCE841@microsoft.com... > > > Is it possible to add a non-table field (i.e. a new field that does not > > > belong to any table) to a view? I am trying to avoid creating a brand new > > > table with just one field so that I can add it to the view. > > > > > > Any help will be very much appreciated. > > > > > > > > > Yes ...
CREATE VIEW foo ( [id] , [name] , my_field ) AS SELECT [id], [name], 'my value' FROM sysobjects Show quote "simon" wrote: > Is it possible to add a non-table field (i.e. a new field that does not > belong to any table) to a view? I am trying to avoid creating a brand new > table with just one field so that I can add it to the view. > > Any help will be very much appreciated. >  |
|||||||||||||||||||||||
Other interesting topics